Advertisment

SQL Server 2005 Integration Services

author-image
PCQ Bureau
New Update

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

Advertisment

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.

Direct Hit!
Applies To: Developers and DBAs



USP: Learn to execute workflow using the control flow runtime
engine of SSIS



Primary Link:
http://tinyurl.com/38qd7b




Google Keywords: SSIS
Advertisment

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

Advertisment

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.

Advertisment

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.

Advertisment

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

Advertisment

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.

Advertisment

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.

Advertisment