June 18, 2009 at 5:39 am
HI,
we are migrating SQL Server 2000 to SQL Server 2005. we have some DTS packages created in 2000 server. we want to migrate SQL Server 2000 DTS packages to 2005 SSIS packages.
I came to know with three methods for migrating process. Can somebody suggest what is the best method to migrate the DTS packages to SSIS packages?
1. Using SQL Server 2005 upgrade adviser
2. Redesiging the new SSIS packages with same functionality of DTS
3. Using migration wizards from BIDS(Businees Intelligence Development Studio)
June 18, 2009 at 6:38 am
Hi,
I would actually go for Upgrade advisor first
And based on that i would go for DTS migrations using SSIS if there are no issues else re designing this in SSIS from scratch if the Upgrade advisor gives you any errors or warnings
Can you try running Upgrade advisor first and see if it gives you any Errors or warnings
Thanks,
Mani
June 18, 2009 at 7:34 am
You will have to re-design your packages in 2005 SSIS and that is the only way out.
We have been doing this dince past year and it is purely Re-WorK..
Again it depends on teh complaxity of your packages. If there are too many tasks and conenctions objects with activex scripts etc.. go for RE-design or if it is a 2 step package .. a simple migration/upgrade would work. some on in our environment are looking at http://www.pragmaticworks.com..
Maninder
www.dbanation.com
June 22, 2009 at 11:14 pm
I downloaded SQL Server 2005 upgrade advisor and installed on my machine. when i trying to access the "Launch Upgrade Advisor Analysis Wizard" it is giving some error message if given instance name. Following is the error message i got:
SQL Server version: 09.00.3073 is not supported by this release of Upgrade Advisor.
Can some body plz assist me to resolve this issue?
June 24, 2009 at 3:17 am
You can run DTS packages completely unchanged in SQL Server 2005 and 2008, in both 32-bit and 64-bit editions.
You need to install the 'DTS Designer' component from the SQL Server feature pack. For SQL Server 2005 many people also find they need to apply the KB917406 fix. For SQL Server 2008 32-bit versions you also need to install the Backward Compatibility components.
If you have any DTS components (e.g. OCX files) that you put into the windows \System32 folder on a 32-bit server, for 64-bit these must instead be put in the windows \sysWOW64 folder.
Ultimately you should plan to rewrite all your DTS routines as SSIS routines, but the DTS support in SQL Server 2005 and 2008 means you can do this when convenient to you, intead of doing everything when you upgrade SQL Server.
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
June 24, 2009 at 7:31 am
I'd recommend re-designing in SSIS anytime! There's so much better functionality that we've combined multiple dts into one ssis in many cases and found much better ways of doing jobs e.g. one data source to extract followed by a multicast to three different data destinations rather than three dts jobs to each read the data and write to one destination.
Ideal for updating development, test and live servers from an outside source e.g. from the Oracle ERP system.
But I'd recommend doing a course on SSIS as it's a whole new package, not just a major change from dts
June 24, 2009 at 8:52 am
I agree with both prev posters.
DTS will run well in the Legacy subsystem until you've had a lot of training & experience with SSIS to be able to redesign the packages.
There are worrisome performance issues with SSIS importing from DB2 (iSeries) large volumes of data - I have posted elsewhere about my own tribulations indicating a 1000 to 1 performance hit in SQL2005. This may come from my own lack of experience with SSIS, but there has been no helping hand from MSDN so far...
If early tests do not show this degradation in your environment, it would be worth moving to SSIS once you know how to take advantage of all the good features.
A word of advice while running DTS in legacy mode: create stored procedure(s) to invoke the DTS package. Scheduling them directly in SQL Agent leads to unintelligible GUID thingos that will not even tell you the name of the package you run...
June 24, 2009 at 9:00 am
Ol'SureHand (6/24/2009)
A word of advice while running DTS in legacy mode: create stored procedure(s) to invoke the DTS package. Scheduling them directly in SQL Agent leads to unintelligible GUID thingos that will not even tell you the name of the package you run...
FYI, you can use the actual DTS Package name in the execute statement. You do not have to use the GUID.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 24, 2009 at 9:03 am
Ol'SureHand (6/24/2009)
I agree with both prev posters.DTS will run well in the Legacy subsystem until you've had a lot of training & experience with SSIS to be able to redesign the packages.
There are worrisome performance issues with SSIS importing from DB2 (iSeries) large volumes of data - I have posted elsewhere about my own tribulations indicating a 1000 to 1 performance hit in SQL2005. This may come from my own lack of experience with SSIS, but there has been no helping hand from MSDN so far...
If early tests do not show this degradation in your environment, it would be worth moving to SSIS once you know how to take advantage of all the good features.
A word of advice while running DTS in legacy mode: create stored procedure(s) to invoke the DTS package. Scheduling them directly in SQL Agent leads to unintelligible GUID thingos that will not even tell you the name of the package you run...
what about creating a SSIS package and using the Execute DTS task? i have to periodically create some data import packages on a SQL 2005 server and i can only do so via DTS because the sybase drivers we use don't work in SSIS. i create a new DTS package and run it from a SSIS package via a SQL job
June 24, 2009 at 9:10 am
vyelchri (6/22/2009)
I downloaded SQL Server 2005 upgrade advisor and installed on my machine. when i trying to access the "Launch Upgrade Advisor Analysis Wizard" it is giving some error message if given instance name. Following is the error message i got:SQL Server version: 09.00.3073 is not supported by this release of Upgrade Advisor.
Can some body plz assist me to resolve this issue?
The 2005 Upgrade Advisor will only analyze SQL 2000 instances. If you already have the DTS packages in SQL 2005, you can use the 2008 version of Upgrade Advisor. It will analyze both SQL 2000 and SQL 2005 instances. Download it here: http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en
Greg
June 24, 2009 at 9:50 am
SQL Noob (6/24/2009)
Ol'SureHand (6/24/2009)
A word of advice while running DTS in legacy mode: create stored procedure(s) to invoke the DTS package. Scheduling them directly in SQL Agent leads to unintelligible GUID thingos that will not even tell you the name of the package you run...what about creating a SSIS package and using the Execute DTS task? i have to periodically create some data import packages on a SQL 2005 server and i can only do so via DTS because the sybase drivers we use don't work in SSIS. i create a new DTS package and run it from a SSIS package via a SQL job
If the result of this in SQL Agent looks like DTSRUN /~Z3453d345345sd345fd34534 and it is acceptable, it's OK - I know it does the job . . . . having inherited massive such jobs, but once I identified what needed to be run, I turned to stored procs.
Personally, I like to know which DTS pkg is scheduled regardless of how the Agent Job is named as the schedule name may or may not be the same as the package name.
Plus, the stored proc gives me a way to
- log PRINT statements with various diagnostics
- conceivably send some global variables / params to the DTS pkg (have not tried it but I know someone posted the working syntax).
- avoid certain unexplainable glitches in firing up said pkgs directly from the Agent Job
- refactor some transforms of the imported data by removing them from the DTS pkg and making them into stored procs (there are problems and glitches running disparate and complex DTS chains, then their logging is appalling once you turn it on so I have a stored proc that works as a master launcher of a chain of DTS packages interspersed with stored procs that massage the data imported from each and copiously PRINT start/stop lines to check in the logs)
Of course SSIS would give me the tools to do all this better ... pity it does not work in my environment!
June 24, 2009 at 12:30 pm
When we migrated from 2000 to 2005, we left the DTS packages as DTS instead of migrating them to SSIS. It seemed like too much work at the time, since they are mostly static. As mentioned above, worry about SSIS later after your migration is complete.
Greg Charles pointed out this great tool in this thread on the same topic:
http://qa.sqlservercentral.com/Forums/Topic659788-146-1.aspx
"... DTSBackup2000, a free download available from SQLDTS.com http://www.sqldts.com/242.aspx. The advantage of using DTSBackup2000 is it can copy all the packages in an instance so you don't have to open each one. I've used it to transfer DTS packages from 5 SQL 2000 instances to new SQL 2005 instances and haven't had any problems with it...."
I moved 200 DTS packages to 2005 with just a few clicks.
June 25, 2009 at 2:45 am
Another advantage of DTSBackup2000 is that it retains all your annotations and object layout within your package.
As far as I know, it is the only automated method that can do this. (Unless you count writing a screen robot for Enterprise Manager as an automated method...)
DTSBackup2000 can be used to import your packages into SQL Server 2005 or 2008, but there is a small gotcha with SQL 2008.
In order to install DTSBackup2000, some support for DTS must already exist on the instance. This is included in the standard Microsoft build for SQL 2005 but not for SQL 2008. I have found the most effective way of getting the required DTS support for SQL 2008 is to install the DTS Designer component before you install DTSBackup2000.
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
June 25, 2009 at 6:52 am
Ol'SureHand (6/24/2009)
SQL Noob (6/24/2009)
Ol'SureHand (6/24/2009)
A word of advice while running DTS in legacy mode: create stored procedure(s) to invoke the DTS package. Scheduling them directly in SQL Agent leads to unintelligible GUID thingos that will not even tell you the name of the package you run...what about creating a SSIS package and using the Execute DTS task? i have to periodically create some data import packages on a SQL 2005 server and i can only do so via DTS because the sybase drivers we use don't work in SSIS. i create a new DTS package and run it from a SSIS package via a SQL job
If the result of this in SQL Agent looks like DTSRUN /~Z3453d345345sd345fd34534 and it is acceptable, it's OK - I know it does the job . . . . having inherited massive such jobs, but once I identified what needed to be run, I turned to stored procs.
Personally, I like to know which DTS pkg is scheduled regardless of how the Agent Job is named as the schedule name may or may not be the same as the package name.
Plus, the stored proc gives me a way to
- log PRINT statements with various diagnostics
- conceivably send some global variables / params to the DTS pkg (have not tried it but I know someone posted the working syntax).
- avoid certain unexplainable glitches in firing up said pkgs directly from the Agent Job
- refactor some transforms of the imported data by removing them from the DTS pkg and making them into stored procs (there are problems and glitches running disparate and complex DTS chains, then their logging is appalling once you turn it on so I have a stored proc that works as a master launcher of a chain of DTS packages interspersed with stored procs that massage the data imported from each and copiously PRINT start/stop lines to check in the logs)
Of course SSIS would give me the tools to do all this better ... pity it does not work in my environment!
create SSIS package and call it what you want
Exec DTS package task
in the task find the DTS package and add it
put the SSIS package on the server and create SQL job
very easy
June 29, 2009 at 8:01 am
I had the sybase issue also. We broke down and finally bought them. Once we were able to get them working the thruput is 98% faster.
-Kim
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply