There are ways to convert DTS packages to SSIS packages, but they don't always work as planned and you can spend a lot of time getting it just right. What does this mean for DTS packages? Do you have to toss them out in order to use SQL Server 2005 and SSIS? Absolutely not. SQL Server Integration Services comes complete with built-in integration for your older DTS packages.
When developing an SSIS package, you have a task available in the control flow called Execute DTS 2000 Package Tasks. This task allows you to add calls to DTS packages built using the SQL Server 2000 tool set. If your DTS package was built in SQL Server 7, you are, as they say, hosed. Using the Execute DTS 2000 Package Task is pretty simple. You drag the task into your control flow and double-click it to configure. That opens the dialog box shown in Figure 1.
Figure 1: Execute DTS 2000 Package Task properties
Requires Free Membership to View
dialog box.
From here, you can specify whether the package is stored on a SQL Server or in a file and provide the connection details to the package. In addition, you are able to set up variables to be passed into the DTS package. This is done in one of two ways. First, from the Inner Variable section of this properties dialog box, click New. From here, you will be able to set the value of any Global Variables that exist inside the DTS package.
You can also add Outer Variables -- variables defined in your SSIS package -- to be passed. You can then use these variables inside your DTS package script objects. Last, but not least, you can tell SSIS to pull the DTS package internal to the SSIS package. To do so, click the
 |
||||
|
|
|||
|
||||
Once you have set all the properties of the Execute DTS 2000 Package Task, it will exist as part of your workflow. You can build further SQL Server Integration Services functionality around the DTS package, manipulating the same data if you wish; or you can simply let the SSIS package act as a wrapper to the DTS package. While converting your Data Transformation Services packages to SQL Server Integration Services allows you greater flexibility, it is sometimes not worth the time it may take to complete the conversion. When that's the case, you can rest easy knowing the option exists to call those old DTS packages right from within your SSIS package.
| ABOUT THE AUTHOR: |
|
Eric Johnson (MCSE, MCITP: Database Administrator, MCSD) is a co-founder of Consortio Services and the primary Database Technologies Consultant. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. He has presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. Eric is active in the local SQL Server Community, serving as the president of the Colorado Springs SQL Server Users Group. Copyright 2007 TechTarget |
This was first published in September 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation