July 1, 2008 at 11:50 pm
Hi,
I want to migrate 80 DTS Packages from SQL 2000 to SQL 2005. I am a newbie and i donot know how to do it, using SQL Server 2005 Upgrade Advisor.
Is there any document available describing step by step guide to migrage DTS to SSIS compatible, As some of the DTS packages are having Active X scripts. So How to migrate them on the server having sql 2005.
Thanks and Regards
July 2, 2008 at 9:09 am
BOL has good information, see "Migrating packages". I also like these articles:
http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1216481,00.html
http://technet.microsoft.com/en-us/library/ms143226.aspx
Also, Brian Knight has developed a tool that migrates DTS packages to SSIS packages. I've looked at it, but haven't tried it. http://www.pragmaticworks.com/dtsxchange.htm
I've been migrating packages starting with simple ones, so I haven't gotten to any complex ones yet.
Greg
July 3, 2008 at 5:36 pm
Hi,
I have downloaded migrationwizard tool from Microsoft website. I want to transfer DTS packages from SQL 2000 to SQL 2005. Since i am a newbie so
When i click on External Tool it prompts an error message saying
Destination Analysis Services Server is not specified.
Press any key to continue......
SQL server 2005 is on one server and SQL 2000 is on another server.
i check the services.msc and all the services related to SQL server 2005 are started
i.e.
SQL Server VSS Writer
SQL Server Integration Services
SQL Server full text search
SQL Server browser
SQL Server Agent
SQL Server
How to do the migration of DTS packages to SQL 2005. I have check these links
BOL has good information, see "Migrating packages". I also like these articles:
http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1216481,00.html
http://technet.microsoft.com/en-us/library/ms143226.aspx
but still cannot do the migration.
Please if someone knows let me know .
Thanks and Regards
July 7, 2008 at 9:11 am
Okay, here's how we've done it. You have to migrate one package at a time.
1. Save DTS packages as files.
2. In SQL 2005 Business Intelligence Development Studio, create a new Integration Services project.
Right-click "SSIS Packages" in Solution Explorer ,select "Migrate DTS 2000 Package" and enter the
path to the DTS package file. BIDS will validate the package and produce the equivalent SSIS
package. You can then build the project and save the package.
Package depoyment is a big subject by itself, so I recommend doing some reading on it or, better still, attend a training class.
You can also try the PacKage Migration Wizard in SQL 2005 Management Studio, though I haven't used it much. You get to it by expanding "Management" and "Legacy" and right-clicking on "Data Transformation Services".
Greg
January 28, 2009 at 10:32 am
Thanks to Greg Charles.
I was badly searching on the inernet for DTS migration.
Found many sites However none of the soution worked for me except
Greg Charle's one. Short and Sweet and ofcourse it worked for me. Thanks Greg.
January 28, 2009 at 2:18 pm
Hi,
Finally i got a breakthrough in migrating DTS Packages from SQL 2000 to SQL 2005 as Legacy Packages.
I found a software called DSTSBackup 2000 which is freeware and you can download from SQLDTS.com.
A very handy software in migrating DTS Packages to SQL 2005. Once you migrated them all then you can migrate them to SSIS.
What you need to do is that after migrating packages , have to refresh the ODBC connections point to the DB on the server running SQL 2005. I transfer 210 Packages all in once using this tool.
The only limitation is that we need to refresh the connections, data transformations.
Before migrating ensure that Backward compatibility should be enabled in SQL 2005 .
Cheers
October 13, 2009 at 9:04 am
Hi,
I am in the same situation as you were before.I am new to both DTS and SSIS 2005.Right now I was able to migrate 2 DTS packages to SSIS 2005 through Package Migration Wizard via SSMS.But when I execute them in SSIS via BIDS, I am getting Active X script errors and I don't know what exactly is the problem and also dont have any idea about how to refresh and change ODBC connections in SSIS.Please help me out to fix this errors and execute the packages sucessfully.
Thanks in advance,
Krishna
October 14, 2009 at 5:17 am
Do you just want to transfer your DTS packages from SQL 2000 to SQL 2005 or SQL 2008? If so then probably the best way is to use DTSBackup2000 to export your packages from the oldf system and import them into the new. You should also download and install the DTS Designer component, which will give you the ability to create and edit DTS packages on the new SQL environment.
Eventually you should migrate from DTS to SSIS, but using the above method you can separate the migration to SSIS from the upgrade to SQL 2005 or 2008.
There are lots of threads giving advice on how to migrate to SSIS. Most of the SSIS experts recommend you develop new SSIS packages from scratch that achieve the same objectives as your DTS packages, rather than trying to do a minimum-change migration. This is because there are a lot of new facilities in SSIS that maybe would not get used on a minimum-change migration but which would make your packages perform far faster.
My advice would be to transfer your DTS to SQL 2005 or 2008 to take advantage of the new database engine, then redevelop your DTS to SSIS when convenient.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply