Run DTS packages within SQL Server Integration Services

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.

[IMAGE]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 O...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Business Intelligence (BI) and Data Warehousing Using the Pivot transformation in SQL Server Integration Services DBA career paths could lead to business intelligence Are data warehouses made for the cloud? Q&A: Business intelligence gets a facelift in SQL Server 2008 R2 Project Gemini gets a new name, Madison earns buzz Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Using package configurations in SQL Server Integration Services (SSIS) How SQL Server 2008 components impact SharePoint implementations Achieving high availability and disaster recovery with SharePoint databases Microsoft SQL Server 2005 End of life comes for SQL Server 2005 SP2, 2008 SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 Microsoft SQL Server 2005 Research SQL Server Interoperability Utilize SSAS for data predictions and classification using Excel How to create a SQL Server linked server to DB2 Export SQL Server data to an Excel file using SSIS and Visual Studio Performance tuning for SQL Server 2005 and Exchange running on SBS Custom VB.Net scripting in SQL Server Integration Services Can SQL Server 2000 work on Windows 2003 platform? Query to search text in old DTS packages in SQL Server? Handle slowly changing dimensions with SSIS 2005 wizard SQL Server Integration Services how-to Compatibility of SQL Server 2005 and 2000 coexisting

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data aggregation  (SearchSQLServer.com) data preprocessing  (SearchSQLServer.com) data warehouse  (SearchSQLServer.com) FileMaker  (SearchSQLServer.com) GIS  (SearchSQLServer.com) MOLAP  (SearchSQLServer.com) pivot table  (SearchSQLServer.com) Quiz: SQL Server 2000  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

uter 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

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.