How to migrate DTS to SSIS and deploy SSIS packages

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Archive: Integration Services How to convert DTS packages to SSIS and overwrite data Query to search text in old DTS packages in SQL Server? Error importing Excel data to SSIS in SQL Server 2005 Delete .bak files automatically with CLR DTS package designed to overwrite extract.xls Displaying a SQL Server database report in Excel Call triggers in a stored procedure Stored procedure practices in SQL Server SQL Server permission to create jobs and schedules in SSIS Problems with structured exception handling 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 SQL Server Migration Strategies and Planning New SQL Server 2008 R2 CTP set for November PASS Summit 2009 Preview Are data warehouses made for the cloud? Q&A: Moving forward with SQL Server in the cloud SQL Server Mailbag: Migrating down to Standard Edition Microsoft releases SQL Server 2008 R2 CTP A first look at Microsoft SQL Server 2008 R2 Using Microsoft Hyper-V for SQL Server consolidation Migrating to SQL Server 2008 and leveraging new features The challenges of SQL Server consolidation

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

First, only deployed SSIS packages are viewable from SQL Server 2005's Management Studio AND in order to see them you will need to connect to your instance of Integration Services NOT to the Database Engine. From the Object Explorer Choose Connect → Integration Services. What are deployed packages? They are packages that have been tested and validated and subsequently transferred to your Integration Services Instance where they can reside as either a file or in MSDB. These are your ready-for-prime-time packages.

From the Migration Wizard, I'd recommend that your destination migrated package be physical .dtsx files. This way you can open, inspect and test your newly converted packages using BIDS. Once you are happy with your SSIS package, you can deploy them to an Instance of SSIS by right-clicking on the SSIS project and choose Properties. From here, you will see the Build and Deployment Utility options in the tree view. You may also consider at this point to locking down and saving your BIDS solutions into source code control.

You can also deploy packages from with Management Studio by connecting to your Integration Services instance from Management Studio and drilling down into the File System and/or MSDB trees. Right-click on either File System or MSDB and choose Migrate. Keep in mind there that SSIS packages can be deployed to either a file system or to MSDB.

As for your last question, the DTS Designer tool allows you to open and modify your DTS packages from SQL Server 2005 Management Studio. From Management Studio, if you connect to a 2000 instance (because this is where your DTS packages reside) and choose Management → Legacy → Data Transformation Services, you will see a listing of DTS packages in that 2000 instance. From here, you can right click on a package and choose Open and you will see what looks exactly like the DTS Design environment under SQL Server 2000. That's it…that is how you access the DTS Designer. The Open choice simply won't be there unless you've installed the DTS Designer from the SQL Server 2005 Feature Pack.