Book Review - Microsoft SQL Server 2008 Integration Services Problem-design-solution

It doesn't matter that this book relates to SQL 2008 because unlike most of the books on the market this book will not explain every element in the current version SSIS in dry detail.

Instead SQL Server 2008 Integration Services Problem - Design - Solution teaches you how to use the tool. It's not a book for beginners it's a book for intermediate and advanced users.

You may have built countless packages during your career and think you're pretty good. That may be the case but the authors too have many years of experience across multiple projects and they've learned what works best.

Even advanced SSIS developers can learn from this text. It might be the case that it validates your approach. if so then great. If not keep reading.

Each chapter covers a different problem. I particularly liked Chapter 2 - SSIS Management Framework Design from which I've used copied the audit schema and framework to use throughout my SSIS development. Chapter 6 - Data Cleansing Design is also good and we're building a generic ETL cleanser based on advice in this chapter for a single customer view implementation. Chapter 9 - SSAS Processing Architecture describes an excellent method processing OLAP cubes using XMLA. I'm really keen to get this implemented but time constraints on my project mean we're still in a drop and rebuild model.

In my opinion this is the best SSIS book on the market.

But it. Read it. Use it.

Building an Auditing Process for SSIS - Part 2

In this post I'l continue where I left off last time with the configuration of an SSIS package which allows the population of an audit schema. 

We use this to support a DBA Dashboard showing the progress of ETL operations and as a method of documenting the package mechanism. I've found administering complex dynamic SSIS packages which use parameters and expressions can be time consuming to debug even for relatively simple issues. In my organisation we now use this audit schema to allow the rapid diagnosis of issues and poor performance.

In a future post I'll blog our DBA dashboard which links into this schema and provides us a snapshot of all SSIS processes in much greater detail than can be derived from SQL Agent errors.

Configuration

Each Package requires a connection manager. Keep this distinct from any other connection managers as a dedicated metadata connection. Configure the connection manager with the sufficient security to write to the audit schema in your chosen Utility database.

Control Flow Elements

The package logic should be wholly contained within a Sequence Container with a SQL task as a pre-requisite step which initiates the BatchLog and another SQL task as a final step which closes that Batch.

Initiate Audit - SQL Task General Pane

The SQL statement reads as follows: EXEC Audit.LogPackageStart @BatchLogID = ?,@PackageName = ?,@ExecutionInstanceID = ?,@MachineName = ?,@UserName = ?,@StartDatetime = ?,@PackageVersionGUID = ?,@VersionMajor = ?,@VersionMinor = ?,@VersionBuild = ?,@VersionComment = ?,@PackageGUID  ,@CreationDate = ?,@CreatedBy = ?

Initiate Audit - SQL Task Parameter Mapping Pane

Initiate Audit - SQL Task Result Set Pane

Staying with the Control Flow pane we move onto the configuration of the Audit Termination.

Terminate Audit - SQL Task General Pane

The SQL Statement reads as follows: EXEC Audit.LogPackageEnd @PackageLogID = ?, @BatchLogID = ?,@EndBatchAudit = ?

Terminate Audit - SQL Task Parameter Mapping Pane

As this post is getting long I'll break here as the configuration of the Control Flow pane is now complete. Next time we'll move onto configuration of the Event Handlers.

Building an Auditing Process for SSIS - Part 1

An audit schema published in the excellent book SSIS Problem– Design – Solution allows the efficient capture of SSIS progress and errors into a simple SQL Server schema. We use this to support a DBA Dashboard showing the progress of ETL operations and as a method of documenting the package mechanism.

SSIS can be difficult to debug, particularly when packages are dynamically driven by variables and expressions or when sub packages are used, but this audit mechanism allows for easier identification of problems and performance bottlenecks.

The SQL Server schema

Attached here is the documentation for the SSIS PDS Audit schema plus a CREATE script. This schema should created in a Utility database or as has been done here in a separate schema with a database named DataWarehouse.

The SSIS Components

The SSIS tasks make use of the following variables. They need to be set up as shown below.

Name	DataType	Value
BatchLogID	Int32	0
PackageLogID	Int32	0
EndBatchAudit	Boolean	False
VariableValue	String	Unable to convert to string

Our standard SSIS Template has these variables and tasks already plumbed in so all new development should use that package as a starting point.

In Part 2 I'll show how your SSIS package should be configured.