Help, how to move DTS pkgs to 2005? 1

[sqlsadie108]

I have 2 servers... one has 2000, the other has 2005.

I would like to try using the Migration Wizard to convert a DTS pkg to an SSIS pkg.

How, and forgive me if I don't sound like I know what I'm doing because I don't, do you "move" a DTS pkg to the 2005 server, so as to be able to run the migration wizard?

I'm afraid to change anything on the 2000 server because that is the live server.

THANKS

 

[mrdenny]

Save the package as a structored storage file. In the SSMS Open the server in the object explorer. Naviage to Management > Legacy > Data Transformation Services. Right click on DTS and Import the package. This will load the package into the SQL 2005 server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

Hi, thanks for the info.

I was able to import under Legacy, but when I attempt to open the package I get this message:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

Is this necessary, or is there another way to view the package using BI studio?

I am a little lost here...

Thanks

 

[sqlsadie108]

Slowly piecing it together... I saved the dts pkg as a SSF, opened it in SSMS as well as BIS.

Ran the migration wizard, and it actually seemed to transform the package to an SSIS pkg (.dtsx)

Fortunately, the pkg is not too complex

 

[fuzzyocelot]

Hi there!

I've been working on the same thing with one of the other DBAs here at work. So I understand how you feel. I've been there myself. This is what we know so far. If I'm wrong on anything, someone please correct me.

Regarding the "SQL Server 2000 DTS Designer Components" message you received... It means that you need to download and install a special component from Microsoft in order to open and run your 2000 packages from within Management Studio even if you're connected to a 2000 instance. Just go to Microsoft's web site and search for "SQL Server 2000 DTS Designer Components". The file name is SQLServer2005_DTS.msi. Once you install it, you'll be able to see, open, edit, and run the packages that are in your 2000 instance as 2000 packages.

Now if you want to "move" your packages to a 2005 instance, there are a couple of things you can do. We recently found out that if you connect to your 2000 instance using Management Studio, open a package under the Legacy folder, right-click and select Save As, you can specify the destination as your 2005 instance. Then you should be able to see the packages under the Legacy folder within your 2005 instance. I don't know if you have to install the DTS Designer Components per instance. I don't remember. Also keep in mind that it won't affect your source packages. This is just a copy.

You can also do the export as a file from 2000 and import it into 2005 if you want to. We made sure our databases had migrated to 2005 okay and then we were able to run our 2000 packages in the 2005 instance just fine (after we changed the data connections to 2005).

This works great for us for because we have probably a good 30+ packages that need to be moved to the 2005 instance. I was trying to migrate them to Integration Services (IS), but it was becoming a HUGE pain as many of the packages are complex and will need to be rewritten for IS. Quite a few of them won't even run due to various errors. It's not going to be pretty for us whenever we do migrate to IS and I'm not looking forward to that.

When you do go through the migration wizard and as I'm sure you figured out, it doesn't change your source 2000 package.

So good luck and I hope you find a good solution that works for you!

Rebecca