SQL Server Integration Services (SSIS) is the newest ETL tool in the SQL Server arsenal. It stands as the replacement to Data Transformation Services (DTS) and is powerful, easy to use and extremely flexible. Being the latest and the greatest, why would you want to continue using DTS? The answer is simple: DTS was around for almost seven years at the time SSIS was introduced in SQL Server 2005. Many organizations use it and some existing DTS packages are complex, having taken days, weeks, or even months to develop. Why reinvent the wheel if your DTS package is working?
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 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
Load DTS2000 Package Internally button. That creates a copy of the DTS package inside your SSIS package and you will no longer be dependent on the source SQL Server or file. Keep in mind that you cannot use SQL Server 2005 tools to edit or otherwise view a DTS package if you store it internally; this option is for storage of the DTS package only.
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
|
|
