ColdFusion Muse

SSIS and DTS - Each Has Uses

You probably know that Microsoft replaced the venerable DTS with something called "SSIS" - which I gather stands for "SQL Server Integration Services". SSIS is immensely powerful and comes with a full featured scripting language and development environment that uses Visual Studio. Practically any kind of data migration and transformation is possible with SSIS. Unfortunately SSIS is also dizzyingly more complicated than the tried and true "Data Transformatin Services" (DTS). In SSIS I have trouble simply finding the list of tables and columns let alone doing transformations. For simple, one time migration tasks it is like using a 5 horsepower tiller to plow up your house plants.

Recently I was moving large datasets from an MS SQL 2000 (32bit) server to an MS SQL 2005 (64 bit) server and discovered that the SSIS package was importing dates incorrectly. It was somehow transforming them into completely different dates (probably due to a format difference or a difference in the way dates are stored). In addition the SSIS wizard did not automatically check the box for "enable identity insert". You might recall that DTS by default checks this flag for any table using the Identity feature. If you create your tables with the Identity property set ahead of time the DTS import will automatically work correctly without the need to edit the import properties of each table. In SSIS however, I have to go into the properties of each table in the wizard and specifically check the "enable identity insert" checkbox.

My brute force solution to these irritaing issues with SSIS is simple. Instead of "importing" using SSIS I "export" using DTS. This is my rule of thumb (at least until I can get SSIS to sing a new tune) - If you are doing straight forward migrations from SQL 2000 to SQL 2005 I recommend that you stick with DTS and keep SSIS for more complex integration needs. If you are interested in integrating the DTS wizard directly into the Server Management Studio, read on:

One of the inconveniences of sticking with DTS is that "Server Management Studio" (SMS) doesn't come with DTS. Fortunately this does not necessarily mean you will have to switch back and forth between Enterprise Manager (EM) and SMS to get the job done. You can get DTS working from inside your SMS installation. Note, in order to follow these instructions you will need to have EM installed as well - but you probably already do for other reasons.

Add DTS as a Tool

You might not know it but DTS is really just a "wizard" that can be run as a stand alone application. You can add it to your SMS installation to run it as an "external tool". Here's the skinny.

1. In SMS go to "Tools-->External Tools"
2. Click "Add" and fill in "DTS Import" in the title
3. Find the path to the file dtswiz.exe for the "command" setting. the default is C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe
4. Add the switches /n/i to the arguments setting

1. In SMS go to "Tools-->External Tools"
2. Click "Add" and fill in "DTS Export" in the title
3. Find the path to the file dtswiz.exe for the "command" setting. the default is C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe
4. Add the switches /n/x to the arguments setting

Now, when you go to the "tools" menu you should see your 2 new tools listed. Click on either one of them and you should see the DTS Wizard pop up as expected.

Now gentle muse readers - feel free and comment on the benefits and uses of SSIS and tell me how easy it is. I look forward to your tips and tricks :)