Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS on SQL 2008 Expand / Collapse
Author
Message
Posted Thursday, March 5, 2009 11:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, Visits: 211
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



Post #669485
Posted Thursday, March 5, 2009 12:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:11 PM
Points: 2,006, Visits: 6,081
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.



Shamless self promotion - read my blog http://sirsql.net
Post #669541
Posted Friday, March 6, 2009 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #670166
Posted Sunday, March 8, 2009 10:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:05 AM
Points: 11, Visits: 146
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.
Post #671245
Posted Tuesday, March 10, 2009 4:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:03 AM
Points: 2,900, Visits: 3,301
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #672262
Posted Tuesday, March 17, 2009 8:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, Visits: 211
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!



Post #677512
Posted Tuesday, March 17, 2009 3:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 1, 2010 4:13 AM
Points: 141, Visits: 394
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
Post #677918
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse