SSIS ETL Control (Part 1)

I can’t stress enough the importance of designing the framework for your ETL prior to getting stuck in with individual packages.

I bring this up, because I have seen that look of dread on a developers face (including my own) when they are about to deploy a package to the live environment and aren’t too sure what gremlins are hiding in the live environment that don’t exist in development. Not only this, with the proliferation of SSIS packages across the corporation, it can become extremely difficult to figure out and manage the process flow across all your servers.

It is for this reason, that I am a big fan of metadata controlled ETL. And I’m not just referring to a configuration table set up as part of an SSIS package, I’m talking about modelling a set of tables within a control database that can hold all the data that a package might need to run. I’m also talking about a generic parent package that can use this meta data to control the execution of any packages defined within the tables.

Amazon Link: The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data

Continue reading “SSIS ETL Control (Part 1)”

SSIS Logging in dbo.sysdtslog90

I was recently asked if you can only use the msdb.dbo.sysdtslog90 table to log SSIS package execution…

It is possible to log your SSIS execution to a database other than msdb, simply by scripting the sysdtslog90 table from the msdb database, and creating it in a new db.

Then when you configure your SQL Server log provider, use a connection manager that points to the database on which you’ve just created your table.

Amazon Link: Microsoft SQL Server 2005 Integration Services Step-by-Step

SSIS Unpivot Component Example

In my previous post on the pivot component, we created a report from a partially populated translation table.

In this post I’m going to show how we re-import our completed report back to our SQL Server table using the SSIS Unpivot component.

Amazon Link: Microsoft® SQL Server™ 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Continue reading “SSIS Unpivot Component Example”

SSIS Pivot Component Example

It may be me, but as yet I haven’t seen too much written about the SSIS pivot and unpivot components.

Whilst they are not the easiest to configure, when faced with the option of importing to SQL Server and using a UNION or the new PIVOT clauses, I actually prefer the SSIS component.

Rather than actually explain the component, I thought it might be useful to go through a real example, so here goes…

Amazon Link:Professional ASP.NET 3.5: In C# and VB (Programmer to Programmer)

Continue reading “SSIS Pivot Component Example”

Tokenize a String

We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.

Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.

The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.

There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…

Amazon Link: Studio 8 Student & Teacher Edition (Includes: Flash Professional 8, Dreamweaver 8, Fireworks 8, Contribute 3, and FlashPaper 2)

Continue reading “Tokenize a String”