Building a New Mouse Trap III: SSIS

Saturday, November 26, 2005

Once the web log files were downloaded and unzipped, I transformed them into an XML document and looked for a way to get the records into SQL Server. My first thought was to learn more about SQL Server Integration Services, the successor to DTS.

Integration Services at Work

I’ve used DTS in the past to fast-load millions of records into data marts. Although the performance numbers were excellent, I’ve always held misgivings about DTS because the packages were opaque, and difficult to maintain and use in generic solutions.

Although I made a fair amount of progress with SSIS, I ultimately ditched the approach for the following reasons:

  • The designer was painfully slow
  • The designer required too much ‘mouse’
  • The designer has bugs

I wanted to like SSIS. SSIS has made vast improvements over DTS. The packages are now XML files, meaning you can look at them, diff them, and check them into source control as text. There are more tasks available, and you can plug-in your own code.

The biggest obstacle was the Script Task would not let me design a script. The reason I wanted to execute a Script Task was to find an elegant solution to inserting a parent record, retrieving an identity value, and inserting child records with the identity as an FKEY. This common task seems to require an inordinate amount of work in SSIS.

Leon Breedt Wednesday, December 7, 2005
The way I use DTS is as follows: Import the raw data (millions of rows too) into SQL Server into some temporary tables for real processing. I don't use any of the special features, don't use packages.

Write up some queries to massage the data how I want it. Use these queries to move the data from the temporary tables into the appropriate normalized tables. Rinse. Repeat.

Brute force and ugly maybe, but I'm leveraging skills I already have, and I can do this FAST. I don't have the time to invest into learning yet another little domain language, only to discover halfway through that I can't do something I really need :)
Comments are now closed.
My Pluralsight Courses
The Podcast!