February 18, 2009 at 1:13 pm
Hi,
We are upgrading sql server 2000 to 2005.We have some DTS packages in sql 2000. I installed the SQL Server 2005 and SP3.Now how to migrate these DTS packages into new sql server 2005?
In 2005 do we need to install SQL Server 2000 DTS Designer Components from the link http://msdn.microsoft.com/en-us/sqlserver/aa336314.aspx and then use dts migration wizard?
correct me if Iam going wrong..
Thanks
February 18, 2009 at 1:22 pm
Just to answer, yes that is what you need to do in order to migrate the packages from 2000 to 2005.
However, depending on the types of packages that you have, they do not all convert very nicely and you may find that you have to rewrite them all together in order to do everything that you need them to do (that was my experience anyway).
Microsoft has this on them
http://msdn.microsoft.com/en-us/library/ms143496.aspx
Specifically
Not all packages can be migrated completely. For example, some DTS tasks do not map to Integration Services tasks, custom tasks cannot be migrated, and some scripts in ActiveX Script tasks or in data transformations cannot be migrated. For more information, see Migrating Tasks and Known DTS Package Migration Issues.
Just something to keep in mind as you go through the process.
February 18, 2009 at 2:30 pm
Do you want to convert them to SSIS, or keep tham as legacy DTS packages like we're doing ?
February 18, 2009 at 2:46 pm
I do not want to make them as ssis packages. I just want to run those dts packages in sql server 2005. Inorder to acheive this what should I do?
If I migrate a dts package from server A(SQL 2000) to Server B(SQL 2005), the dts package in Server A will be removed from server A?
February 18, 2009 at 3:48 pm
kotlas7 (2/18/2009)
I do not want to make them as ssis packages. I just want to run those dts packages in sql server 2005. Inorder to acheive this what should I do?If I migrate a dts package from server A(SQL 2000) to Server B(SQL 2005), the dts package in Server A will be removed from server A?
You don't want to use the Package Migration Wizard. See this thread: http://www.sqlservercentral.com/Forums/Topic655815-146-1.aspx#bm655903.
Greg
February 18, 2009 at 4:02 pm
To Export:
Map a drive to a location to save your exported DTS packages: eg. server\DTS_Exports
Use SQL 2005 Management Studio
Copy DTS name into your clipboard.
Right click the DTS “Export”
Highlight the DTS icon if displayed
Paste the DTS name into the file name and save
To Import:
Open SQL 2005
Go to “Management” -> “Legacy” -> “Data Transformation Services”
(no need to open DTS folder)
Right Click “Import Package File”
Select the file from Mapped Drive location
February 18, 2009 at 4:08 pm
Thanks Greg,
I have a dts package in sql server 2000 in Data Transfer Services->Local Packages->ABC(dts package name in the right pane)
From here How can I export to file system? If I export to path c:\dts, then from sql server 2005 In Data Transfer services I can import the package from c:\dts right?
Thats all about?
Plz tell me how can I export a file system in sql 2000?
Thanks for your help
February 18, 2009 at 4:21 pm
Please clarify me which one of the following I need to do
1.
In sql server 2000 in Data Transfer Services->Local Packages->ABC(dts package name in the right pane)->right click the package->all tasks->export data
2.In sql server 2000 in Data Transfer Services->Local Packages->right click->export list
and if we export the dts package from sql 2000, it will be removed or it will stay in sql 2000 too...
thanks
February 18, 2009 at 4:41 pm
Neither #1 or #2
I updated my instructions. Use SQL 2005 for the export step, not SQL 2000.
February 18, 2009 at 4:42 pm
You either have to open each package in Enterprise Manager and save it to a file or use 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.
Use the method HomeBrew01 describes to import the packages into SQL 2005.
PS Now I understand HomeBrew's export method. It'll work as well, but you still have to export one package at a time.
Greg
February 24, 2009 at 11:52 am
Greg Charles (2/18/2009)
You either have to open each package in Enterprise Manager and save it to a file or use 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.Use the method HomeBrew01 describes to import the packages into SQL 2005.
PS Now I understand HomeBrew's export method. It'll work as well, but you still have to export one package at a time.
Awesome tool ! Much better than my method. Just a few clicks to do the whole process. Thanks for the info.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply