Developing Matt

My Technical Journal

Preemptive Research on Migrating DTS to SSIS

with 2 comments

I’m working on a project to migrate dts packages to ssis 2008.  My mother always told me that an ounce of prevention is worth a pound of something.  I can never remember what that something is but it’s still a good saying.  Anyway, here are some things I have learned along the way as I’ve been looking at migration to ssis 2008.  Also check out the script for exploring the dts packages.

Step 1: check out the upgrade adviser to give you a peek into how much work you actually have to do and to find out what elements you are going to have to rebuild from scratch. This will give you an idea of what packages you will have to completely re-write and which ones will play nice.

Step 1a: Along the way you might want to install the SQL 2000 DTS Designer Components (read the rest of 1a before downloading) so that you can open up the existing dts packages and work on them in 2005 or 2008. I don’t recommend doing this unless you really don’t want to install sql 2000 on your machine. If you do, then you might have a little corruption issue with sql 2000:

“The procedure entry point ?ProcessExecute@@YAXPAUHWIND-@@PBG1@Z could not be located in the dynamic link library SEMSFC.dll”

The fix: Several posts recommended to upgrade to sp4 for sql 2000 (link) but I received the following error when I attempted this: ‘The contents of this file cannot be unpacked. The executable you are attempting to run has been corrupted. Please obtain another copy of the file, verify its integrity, and try again.’ This could be due to a corrupt file or it could be due to the fact that my database was corrupted. Another possibility is user error, but that is unlikely with this user as he has a protective narcissism wall around him. Since it was my own machine I uninstalled sql 2000, reinstalled it and applied the sp4 to it from disk. Not wise as I already had 2005 and 2008 on my machine as well, but so far I haven’t seen any problems and I can now manage/change those packages from ssms. Yes, you are right. I didn’t really need to download this as I already had 2000 on my machine, but I thought it might have extra benefits somewhere. It didn’t…just an external app that is basically dts from sql 2000.

Step 1b: When you run the upgrade advisor wizard it will show you 2 warnings (or more) and at least 4 messages. Make sure to look at the ‘show affected objects’. You will get these warnings and messages even if you don’t have any packages on your server. I was worried with the big fat “META DATA SERVICES PACKAGES ARE NOT SUPPORTED” and spent an hour trying to figure out why my 1 test package was so screwed up even after the supposed fixes. Turns out there will always be 1 object. If you don’t have any packages you will get these errors showing ‘1 affected objects’

Step 2: Run the Package migration wizard. I’m told that it will migrate 40% of your package to the new SSIS format. The caveat here is that the other 60% will be lumped into an embedded dts 2000 package task. It will work and port over the dts packages…but don’t assume that you are out of the ‘depreciated’ zone. You aren’t. Those items will have to be rewritten. For me I am looking at 221 packages that contain over a thousand of these embedded dts 2000 tasks that I’m going to have to rewrite while parachuting from outer space. OK. I made up that last part about parachuting from outer space. It was a ploy for sympathy.

Other things learnt so far (These items are from the book Microsoft Sql Server 2005 Integration Services by Kirk Haselden – great book Kirk!):

· Any active x transformations cannot be migrated
· Most ActiveX scripts (40%) will not be migrated
· Most Transform Data Tasks (40%) will not be migrated (and will be renamed to Data Flow)
· Most Data Driven Queries (40%) will not be migrated (and will be renamed to ‘Execute DTS 2000 Package’)
· Bulk insert tasks will migrate 70% of the time
· Analysis services will not migrate
· Dynamic properties will not migrate
· Access connections have a 95% success rate
· OLEDB: OLEDB Provider has a 99% success rate
· Migrate settings will be ignored (exception: global variables): packge priority class, logging settings, fail package on first error, etc.


Written by matt

January 20, 2009 at 6:24 pm

Posted in SSIS

2 Responses

Subscribe to comments with RSS.

  1. Hey man, I used DTS xChange to convert all my packages. Did almost all of them perfectly, the M$ wizard really blows.


    June 18, 2009 at 8:16 am

  2. thanks for the comment JACK! Since this post I’ve heard excellent reviews regarding dts xchange. thanks for pointing it out!


    June 18, 2009 at 8:58 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: