Wiley Professional SQL Server 2005 Integration Services 978-0-7645-8435-0 User Manual
Product codes
978-0-7645-8435-0
Welcome to SQL Ser ver
Integration Ser vices
SQL Server Integration Services (SSIS) is one of the most powerful features in SQL Server 2005. It
is technically classified as a business intelligence feature and is a robust way to load data and
perform tasks in a workflow. Even though it’s mainly used for data loads, you can use it to do
other tasks in a workflow like executing a program or a script, or it can be extended. This chapter
describes much of the architecture of SSIS and covers the basics of tasks.
is technically classified as a business intelligence feature and is a robust way to load data and
perform tasks in a workflow. Even though it’s mainly used for data loads, you can use it to do
other tasks in a workflow like executing a program or a script, or it can be extended. This chapter
describes much of the architecture of SSIS and covers the basics of tasks.
What’s New in SQL Ser ver 2005 SSIS
In SQL Server 7.0, Microsoft had a small team of developers work on a much understated feature of
SQL Server called Data Transformation Services (DTS). DTS was the backbone of the Import/Export
Wizard, and the DTS’s primary purpose was to transform data from almost any OLE DB–compliant
data source to another destination. It also had the ability to execute programs and run scripts,
making workflow a minor feature.
SQL Server called Data Transformation Services (DTS). DTS was the backbone of the Import/Export
Wizard, and the DTS’s primary purpose was to transform data from almost any OLE DB–compliant
data source to another destination. It also had the ability to execute programs and run scripts,
making workflow a minor feature.
By the time that SQL Server 2000 was released, DTS had a strong following of DBAs and developers.
Microsoft included in the release new features like the Dynamic Properties task to help you
dynamically alter the package at runtime. It also had extended logging and broke a transformation
into many phases, called the multiphase data pump. Usability studies still showed that at this point
developers had to create elaborate scripts to extend DTS to do what they wanted. For example, if you
wanted DTS to conditionally load data based on the existence of a file, you would have to use the
ActiveX Script task and VBScript to dynamically do this. The problem here was that most DBAs
didn’t have this type of scripting experience.
Microsoft included in the release new features like the Dynamic Properties task to help you
dynamically alter the package at runtime. It also had extended logging and broke a transformation
into many phases, called the multiphase data pump. Usability studies still showed that at this point
developers had to create elaborate scripts to extend DTS to do what they wanted. For example, if you
wanted DTS to conditionally load data based on the existence of a file, you would have to use the
ActiveX Script task and VBScript to dynamically do this. The problem here was that most DBAs
didn’t have this type of scripting experience.
After five years, Microsoft released the much touted SQL Server 2005, where DTS is no longer an
understated feature, but one of the main business intelligence (BI) foundations. It’s been given so
much importance now that it has its own service. DTS has also been renamed to SQL Server
Integration Services (SSIS). So much has been added to SSIS that the rename of the product was
understated feature, but one of the main business intelligence (BI) foundations. It’s been given so
much importance now that it has its own service. DTS has also been renamed to SQL Server
Integration Services (SSIS). So much has been added to SSIS that the rename of the product was
04 584359 ch01.qxd 12/16/05 10:20 PM Page 1
COPYRIGHTED MATERIAL