March 5, 2009 at 11:42 am
Hello all,
We are moving from SQL 2000 to SQL 2008 in a side-by-side migration. Our shop has hundreds of DTS packages that we do not have time to migrate SSIS. The plan is to copy the packages and run them as is until a full migration can be preformed.
We need to move the packages in mass. We tried copying them directly from sysdtspackages, but some of the packages would not open. Saving them individually is an option, but it will take time and it does not preserve history. Any thoughts on how to accomplish this would be appreciated.
Thanks
March 5, 2009 at 12:32 pm
If you don't have to take the old server down just run them from there, and migrate as you are able to turn them into SSIS packages.
March 6, 2009 at 6:50 am
You've just hit the hardest part of the migration out of 2000, retiring DTS. I'd suggest a mixed approach. Run the Upgrade Adviser and let it scan your DTS packages. It will identify any that can simply be moved into SSIS. Move those. Change the connection strings on the others, leaving the 2000 server in place, as was previously suggested, and start a vigorous campaign to upgrade the remaining packages.
That's the plan we're implementing right now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 8, 2009 at 10:22 pm
Basically you have several options, one option is to retain current server to allow the DTS packages continues to run from there, then proceed other databases upgrade to SQL2008. But you have to change all DTS packages connection strings to point to the new SQL2008 databases.
Second option is to migrate all DTS packages to the new SQL2008 environment, but run them as DTS packages. In this case, you have to setup all the related scheduled jobs manually, because the DTS functions are not the same like in SQL2000. During the course of migration of the packages, there are quite a number of things you will find not working anymore. It is because Microsoft only provides a backward compatibility channel for people to choose to continue to run DTS packages as per before, but there are so many things that not being taken care of. E.g. if your SQL2000 get used to use UDF using "System_Function_Schema", then it won't work anymore.
Third option is to convert or upgrade all DTS packages to SSIS packages. You can run the Migration Wizard which provided by Microsoft, but you will find it doesn't do the job completely. There are some tasks the wizard could not do, e.g. if your DTS packages using "ActiveX Scripts tasks", then the wizard only creates a blank task, all your scripts inside are useless. Or if your packages are using "Dynamics Properties Tasks" to pass values via global variables, then you will find they will not work anymore. All these problems will cost you a lot of time to amend the packages one by one.
Four option is to seek for some external software like DTSXchange, but they will charge you based on number of packages need to be upgraded. If your company can afford that, then that could be the best option. If not, then you have to find your way.
March 10, 2009 at 4:18 am
You can run your DTS packages unchanged on SQL Server 2008. There are a lot of advantages to be gained from migrating them to SSIS, but I'll leave those aside.
You need to install the DTS Designer component from the SQL Feature Pack. If you are running a 32-bit server you must also install the 'Backwards Compatibility' components, but on a 64-bit server these are included by default.
You also need to follow the instructions about copying some of the DTS Designer files given in the Readme to get the Designer to be visible in SSMS and BIDS. As well as copying the DLLs, you also need to copy the RLLs from the original \resource\1033 (or whatever your locale is) folder to the \resource\1033 folders for SSMS and BIDS. BIDS does not have a \resource folder so you have to create it.
Any DTS components you install in the windows \system32 folder on a 32-bit server (e.g. OCX files) must be placed in the \sysWOW64 folder on a 64-bit server.
To export and import in bulk, I recommend using the freeware 'DTS Backup 2000' tool. In order to install this on SQL Server 2008, you must have already installed the DTS Designer, otherwise the DTS Backup 2000 install fails. You can also use spDTSExportPackages and spDTSImportPackages procs found in SQL Server FineBuild, or roll your own.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 17, 2009 at 8:32 am
I've been away for a few days, but much thanks for all the replies. We have over 500 DTS packages in our environment, so the migration to SSIS will be substantial for us. Most likely, we will implement a phased approach, migrating packages in logical units.
Thanks!
March 17, 2009 at 3:12 pm
Hi,
I am fully recommend what EdVassie saying. I have done the some thing in SQL 2005 when I migrate my DTS packages from SQL 2000. Because migrate to SSIS package from normal sql 2000 DTS package is not easy, sometime if you already used ActiveX script inside the package, it won't convert in SSIS.
Leo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply