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
The result should look like this:



The procedure for adding "export" is pretty much the same.
  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
The result should look like this:



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 :)

Comments
James Moberg's Gravatar During a recent 2000-2005 migration, I was so discouraged with how long it was taking trying to set up each task that I finally resorted to detaching the 2000 dataset and reattaching it in 2005. It worked fine without any problems (except for the 2 minute downtime.)

I also use Toad for SQL Server Freeware:
http://www.toadsoft.com/toadsqlserver/toad_sqlserv...
# Posted By James Moberg | 5/27/08 5:54 PM
Mike Kelp's Gravatar I totally agree with your assessment of SSIS thus far. We ran into trouble with SSIS the moment international characters came into play and couldn't find a solution after 3 days of googling and diving into a massive book on SSIS to do a simple import.

While I really like the intentions of SSIS it upsets me that a simple operation is made so hard, with no explanation to be found. I think part of it is that MS insists on really complicated, proprietary names for every operation to the point that you can't make a guess at what to search for haha.

Mike.
# Posted By Mike Kelp | 5/27/08 9:20 PM
Mike Kear's Gravatar I have never been able successfully to run a saved job from SSIS. It looks like it's doing something, but in fact no data transfer takes place.

So I have to open the project in Visual studio, and run it from there - with debugging and all. Even without any changes to the task - simply repeating what was done last time, takes about 50 times longer than it used to with DTS, and it cant be run automatically.

In SQLServer2000, i used to have my remote-hosted databases copied to my dev server each night, so i had an additional backup of the live databases, and when i was developing i was working with real data not some simulated 'play' data. Now, since each task takes about 15-30 minutes, and i have 45 databases to work on, i can't do it nightly automatically. I spend all Saturday afternoon doing it manually once a week.

I do hope, sooner or later, someone will show me how to have a SSIS job run automatically. Or even just run from a command line - i could set up a scheduled task to run it if so.

I HATE SSIS!!!!!!! it has made my database management sooooo DIFFICULT. And in my opinion there was no need to improve on DTS, which is no longer supported on SQLServer. (in fact i tried to follow this procedure, and the wizard is not on my machine.)
# Posted By Mike Kear | 5/27/08 10:29 PM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.