Chapter 1: Welcome to SQL Server Integration Services

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.

What's New in SQL Server 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.

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.

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 most appropriate. Microsoft made a huge investment in usability and making it so that there is no longer a need for scripting.

Most of this book will assume that you know nothing about the past releases of SQL Server DTS and will start with a fresh look at SQL Server 2005 SSIS. After all, when you dive into the new features, you'll realize how little knowing anything about the old release actually helps you when learning this one. The learning curve can be considered steep at first, but once you figure out the basics, you'll be creating what would have been complex packages in SQL Server 2000 in minutes.

You can start differentiating the new SSIS by looking at the toolbox that you now have at your fingertips as an SSIS developer. The names of the tools and how you use them have changed dramatically, but the tools all existed in a different form in SQL Server 2000. This section introduces you briefly to each of the tools, but you will explore them more deeply beginning in the next chapter.

Import and Export Wizard

If you need to move data quickly from almost any OLE DB-compliant data source to a destination, you can use the SSIS Import and Export Wizard (shown in Figure 1-1). The wizard is a quick way to move the data and perform very light transformations of data. It has not changed substantially from SQL Server 2000. Like SQL Server 2000, it still gives you the option of checking all the tables you'd like to transfer. You also get the option now of encapsulating the entire transfer of data into a single transaction.

Figure 1-1

The Business Intelligence Development Studio

The Business Intelligence Development Studio (BIDS) is the central tool that you'll spend most of your time in as a SQL Server 2005 SSIS developer. Like the rest of SQL Server 2005, the tool's foundation is the Visual Studio 2005 interface (shown in Figure 1-2), which is the equivalent of the DTS Designer in SQL Server 2000. The nicest thing about the tool is that it's not bound to any particular SQL Server. In other words, you won't have to connect to a SQL Server to design a SSIS package. You can design the package disconnected from your SQL Server environment and then deploy it to your target SQL Server you'd like it to run on. This interface will be discussed in much more detail in Chapter 3.

Figure 1-2