Whether you plan to migrate SQL Server Data Transformation Services (DTS) packages to SQL Server Integration Services (SSIS) or run DTS packages in SQL Server 2005, this expert advice can help. We've tallied our Top 10 tips on the topic of SSIS, so you'd have all the most searched for information in one source. If you've already implemented the new ETL platform into your SQL Server environment, check out the tips on SSIS maintenance, debugging and programming basics.
#1 -Migrating SQL Server 2000 DTS packages to SSIS
SQL Server Integration Services (SSIS) is more than a replacement for SQL Server 2000's Data Transformation Services (DTS) packages. The scale of changes is vast and the SQL Server 2005 ETL platform more powerful. SQL Server expert Joe Toscano helps make the migration to DTS as painless as possible with the transitional tools SQL Server 2005 Upgrade Advisor and Package Migration Wizard.
#2 -Maintain DTS packages in SQL Server 2005
What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? In this tip, you'll see how to edit, maintain and even develop existing DTS packages in SQL Server 2005 using the SQL Server DTS Designer Tool. You'll also see some migration challenges and how to use the SQL Server 2005 Upgrade Advisor.
#3 -Run DTS packages within SQL Server Integration Services
Do you have a DTS package that performs just right in SQL Server 2000 and you'd like to have it do the same in SQL Server 2005? A method does exist. SQL Server expert Eric Johnson walks you through the steps to run a DTS package in your SSIS package.
#4 -SSIS maintenance plans in SQL Server 2005
Using SSIS packages for your maintenance plans in SQL Server 2005 allows greater flexibility to customize tasks and also provides an execution history for less difficult troubleshooting. Contributor Baya Pavliashvili highlights how maintenance plans have improved in the latest version of SQL Server and walks you through building your own plan with the Maintenance Plan Wizard.
#5 -Extend SSIS packages with scripting and programming
SQL Server Integration Services brings new meaning to importing data, compared to Data Transformation Services. Microsoft also offers two methods of extending SSIS functionality. Contributor Serdar Yegulalp shares how to extend Control Flow and Data Flow with scripting, and also with the advanced method involving programming with custom objects.
#6 -SQL Server Integration Services to simplify database maintenance
The SQL Server Development Team at Microsoft adapted SSIS to offer point-and-click database maintenance, offering vast improvements over DTS and SQLMaint in SQL Server 2000. The Database Maintenance Wizard
still exists to build the initial process, but the modifications are all made to an SSIS package stored locally on the server. In this tip, you'll see how a little maintenance can go a long way in improving SQL Server performance.
#7 -Debugging SSIS packages in SQL Server
Debugging SSIS packages is a feature in SQL Server 2005 that offers many methods for finding the cause of a problem. Here's a step-by-step process for two of these methods: breakpoints and data viewers. Both methods present a lot of information about the inner workings of your packages so you can get under the hood and tune SSIS performance.
#8 -Debugging SQL Server DTS packages
Still weighing whether to move your DTS packages to SSIS? As a SQL Server DBA, you frequently modify DTS packages, either to correct errors in logic, extend DTS package functionality or to change the server, database or object name. SQL Server MVP Hilary Cotter shares various methods for debugging and promoting DTS packages in SQL Server 2000.
#9 -SQL Server Integration Services programming basics
SSIS is more than a tool to move data around in SQL Server 2005. It creates a system where the data sets you import and the packages you create are programmatic objects that can talk to one another, rather than existing as static data. Here's an outline of some SSIS features and why they make it an impressive ETL tool.
#10 -T-SQL in SSIS: The power and the weaknesses
Using T-SQL vs. the Data Flow task in SQL Server Integration Services is a decision dependent on a number of factors. Serdar Yegulalp shares some things to consider, including data origin, operation complexity, stored procedure purpose and whether the data is likely to change in SSIS.
