Top 10 SQL Server Integration Services (SSIS) and DTS tips

15 Jun 2008 | SearchSQLServer.com

Expert advice on database administration

Digg This!     StumbleUpon     Del.icio.us   

#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

More on SSIS and DTS:

Visit our "Ask the SQL Server Expert" center for more on SSIS

#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.

Digg This!     StumbleUpon     Del.icio.us   

RELATED CONTENT

SQL Server data warehousing/business intelligence

Manage traces in SQL Server 2005 Analysis Services with XMLA commands

Tutorial: SQL Server 2005 Analysis Services

Open SSIS packages without validation using these SQL properties

How to process SQL Server 2005 Analysis Services for data availability

Export SQL Server data to an Excel file using SSIS and Visual Studio

Five steps to event handlers in SQL Server Integration Services (SSIS)

Workaround to Reporting Services error in SQL Server Express

Optimize SAN setup for improved SQL Server performance

Table partitioning with SQL Server 2005

Synchronizing Analysis Services 2005 databases in SQL Server

SQL Server upgrades and patches

Upgrade live applications to SQL Server 2005 for high availability

SQL Server high availability when upgrading to SQL Server 2005

How to restore SQL Server database to transition server during upgrade

Upgrade Active/Active cluster to SQL Server 2005 and Windows 2003

FAQ: SQL Server databases how-to

Upgrading to SQL Server 2008 advantages and hardware requirements

Create an upgrade plan for your move to SQL Server 2005

Table partitioning with SQL Server 2005

Change collation to SQL Server 2005 after upgrading databases

Upgrade databases to SQL Server 2005 after server upgrade

SQL Server integration/interoperability

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

Run DTS packages within SQL Server Integration Services

SQL Server Blog Watch

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