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.