Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS on SQL 2008


DTS on SQL 2008

Author
Message
Brookstone
Brookstone
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
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



Nicholas Cain
Nicholas Cain
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 6200
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17617 Visits: 32267
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
yu_wai
yu_wai
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3152 Visits: 3819
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "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
Brookstone
Brookstone
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
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!



Leo-696352
Leo-696352
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search