SQL Server 2005 Integration Services

Vinod Unny, Enterprise InfoTech

SQL Server's DTS (Data Transformation Services) was a very useful tool in copying or moving data from one server to another, as well as transforming a good bit of it on the way to a different database schema. In SQL Server 2005, DTS has been replaced by a completely new engine named SSIS (SQL Server Integration Services).

SSIS allows developers and DBAs to perform all the tasks of DTS and much more-in a very easy and intuitive manner.

SSIS is a set of few components. It consists of a Windows level service that is used to store, enumerate and stop SSIS 'packages'. Note that the service is not responsible for executing the package.

A package is a combination of many different things that SSIS as a whole is capable of and is the basic unit of work, deployment and execution.

The other two parts of SSIS are the two runtime engines-both responsible for the execution of different parts of the package. The first runtime is called the Control Flow Runtime and executes the 'workflow' like part of the package. This article takes a deeper look into this part of the runtime. The other runtime is the Data Flow Runtime and is responsible for executing data transformation instructions within the package. We'll delve deeper into this aspect in the next part of this article.

So let us now take a look at creating an SSIS package and executing it. You will need to have SQL Server 2005 Database engine and SQL Server 2005 Integration Services installed on a machine. This will also include the SQL Server BIDS (Business Development Studio) that allows you to program SQL Server with a special edition of Visual Studio 2005. Open BIDS from the SQL Server group and select a new Integration Services project. This creates a new SSIS project and opens the design surface in VS. The two important tabs are right on top-Control Flow and Data Flow. These tabs tell you what you are working on currently.

To create a new SSIS project, open BIDS from the SQL

Server group in the Business Development Studio and

select a new Integration Services project

Since we are currently working on the Control Flow, we must understand the different items that a control flow can have. Control flows can have the

following objects in the workflow:

Containers: Logical or procedural grouping of sub-workflows within a particular package. These containers can be anything from loops (like for

and foreach) to sequence and other groups.

Tasks: Discrete items of work or activities that can be performed in a control flow. Tasks can be grouped together by using the container objects mentioned above.

Precedence Constraints: These are basically the order in which tasks have to be executed. These constraints can also be for each task's success, failure or completion.

Variables: Allow you to define different values that can be shared across different tasks as well. 

 Let us create a simple Control Flow that does the following. The control flow should e-mail certain customers with a new mailer as well as inform the management on any error and completion. For this, we shall proceed as the following.

First drop an 'Execute SQL Task' into the Control Flow area. Configure the properties of this so that it returns a full record set for the SQL query: 'select firstname, emailaddress from Dimcustomer'. Now drop a 'Foreach Container' and edit its properties to create two variables from the 0 and 1 index of the recordset obtained above.

The completed Control Flow in the designer.

Note the green, red and blue constraints that

denote paths for success, failure and completion

Drop a Send mail task inside the foreach container and configure it to use a new SMTP connection as well as the ToLine parameter to take the e-mail address variable created above. Once this is done, drop two more Send mail tasks outside the container.

Configure for a success message to the management and the other for a failure message. Drag a new precedence constraint from both the Execute SQL task as well as the foreach container to the failure one and one from the foreach to the success one. Right click the ones leading to the failure tasks, select Failure in the menu and right click the one to the success task and select completion. You will see the lines turning red and blue for the failure and completion processes.

Congrats, you have just finished your first SSIS package. To run this package you can right click the package name in the Solution Explorer and

select Execute.

Control Flow in a package. Green shows tasks successfully

completed, yellow shows tasks that are still executing and

red shows tasks (or containers) that have had an error

You will see the different states in the graphical design-yellow while executing, green on success, red on failure and blue on completion. This is

a simple example of a SSIS workflow or Control Flow.

As you can see in the toolbox, the number of different tasks you can do completely graphically is quite large and you can get even more online. All you need to do is design your flow and then implement it as a control flow in SQL Server.

Next month, we'll take a look at how you can perform an ETL operation (Extract, Transform & Load) using the Data Flow component of SQL Server Integration Services.

Source: PCQuest