This article focus on the concept that SQL Server 2000 DTS packages can be run in SQL Server Management Studio (SSMS) 2005/2008 without modification.
Until last week I do believe that SQL Server 2000 DTS packages can not be run on SSMS (SQL Server Management Studio) 2005/2008 directly. The only way was to use the Execute DTS 2000 Package Task in your control Flow.
We can run our SQL Server 2000 DTS packages with SSMS 2005/2008.
The very first thing required is your SQL Server 2005/2008 should have following components installed.
- Microsoft SQL Server 2005 Backward compatibility Setup
- Microsoft SQL Server 2000 DTS Designer Components
You can find the #1 in your SQL Server Setup DVD or download it from Microsoft site as per your current SQL environment.
You can find the #2 from Microsoft Download Center http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988
After obtaining the two components you need to install it on your SQL Server box
Once you installed the two components. You can open your SSMS (SQL Server Management Studio).
SSMS managed the DTS packages under Management->Legacy->Data Transformation Services
You can Open or Import an existing DTS package by right-clicking on Data Transformation Services
You can select an existing DTS package and it will appear under the Data Transformation Services tree. To open the DTS package right-click on the package name and select Open.
Your DTS package will open in the DTS 2000 Designer window.
You can run your DTS package and do any modification you like to do inside DTS 2000 Package Designer.
Note: Till the time your DTS 2000 Package Designer window is open you can not work with other SSMS windows. You need to close it first.
Moral of the story is you do not need to re-write your existing DTS packages into SSIS packages. You can run them from SSMS 2005/2008…but this do not mean you do not need SSIS further….SSIS is a different platform then the DTS and comes up with so many advantages which are difficult to ignore.
Please add your comments to improve this post or post your suggestions as you like.
