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:
Read More3 Comments
-
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... -
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. -
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.)
Sites to Watch
Aggregators
RSS
