Building a New Mouse Trap III: SSIS

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.

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.