Introduction

Previously, I wrote how SQL Server Integration Services (SSIS) has much improved data transformation features than its predecessor. But what makes this tool even more special is the ability to perform more advanced capabilities. For instance, you can perform repeated iterations in a for each loop container, perform FTP operations with the FTP task, collect WMI information using the WMI Data Reader/Event Watcher task, and connect to Web Services with the Web Service task. This article will discuss some of this.

Expanding from the Data Flow

Previously, I talked about using the data flow. Data flow tasks are dragged/dropped into the control flow, so you can create one or more data flow tasks in a single package. Consequently, there is one control flow, so consider the control flow the master package execution. Both the control flow and data flow have different tasks, and in control flow, there isn’t an errored data path; however, right-clicking on the arrow between tasks can change the state from Success to Failure or Completion.

Dynamic Menus

When using certain tasks, you will notice some of the property options change when selecting different options. For instance, some properties change based on the connection manager, to include connection-specific settings information. Others change on different properties, like the Execute SQL Task’s SQLSourceType property.

Database Maintenance Control Tasks

There are several maintenance task operations for database administrators. This gives you the ability to back up databases, rebuild or reorganize indexes, shrink databases, transfer objects, etc.

General Control Tasks

Some of the more common control tasks you may use are briefly described below:

• For Loop Container – Loops through repeatedly until the conditional expression evaluates to false. Whatever is within the container is repeated over and over again until the container is exited.
• For Each Loop Container – This task allows you to loop through a variety of collections, including files, xml nodes, SSIS variable objects, etc. Whatever is within the container is repeated over and over again until the container is exited.
• Sequence Container – Organize a set of tasks into a container, fired sequentially.
• Execute SQL Task – This task executes a SQL query, stored procedure, or SQL file.
• Execute Package Task - This task can execute another package.
• Bulk Insert Task – This task inserts a text file into a table pretty efficiently; however, you do not have the ability to control how the data comes in.
• File System Task – This task has the ability to perform a variety of file system operations and should cover everything you may need to do with the file system.
• FTP Task – This task allows you to send/receive files through FTP. The connection manager requires the setup of the FTP server.
• Execute Package Task – This task executes a child package within the current package’s context.
• Script Task – The script task is meant to replace the ActiveX Task, by using VB.NET script to perform any advanced scripting features you will need.
• Send Mail Task – This task sends an email through SMTP, not MAPI as in DTS.
• XML Task – This task deals with the variety of operations you can perform with XML data.

Setting Up Variables

In SSIS packages, the package can contain global or task-specific variables that are accessible in the package. Certain tasks can take advantage of a variable value, or they can be used to replace existing task/connection values through the Expressions collection. Expressions work similar to the Dynamic Task in DTS. Expressions map to properties on a task or connection, and override the value with the variable value. For instance, in the package provided with the source, I override the Cheap File and Expensive File connection managers to use an expression that stores the path files for each. This path variable is globally accessible, so I can also use it in other places, like the File System task that deletes the files at the end of the workflow.

Let's take a look at the setup of an expression. By selecting it, and viewing the properties, you will notice an Expressions collection in the property window. Select the cell, and you will see a browse button pop up. After clicking it, the following editor appears. Notice that the second editor, the Expression Builder, allows you to build a complex expression based on variables, constants, and other objects.

Figure 1: Expressions designer available in most tasks

Certain tasks allow you to use a variable instead of a static value or item in a list, meaning a drop down will appear of variable names, instead of predefined values. This can occur when replacing the destination path of the file system task, for example.

Setting Up Error Handling

Previously, for several design reasons, you couldn't have a single error step for multiple tasks. You had to break them out because of how the DTS workflow operated. A single email task can be used, which leverages SMTP to send an email. The following settings are set using the editor.

Figure 2: Email task allowing you to specify a connection

Event Handling

However, a better way than to use error handling is to setup error conditions in the Event Handlers tab. Unders this tab, you have access to a designer that allows you to setup one or more tasks to perform error handling. You can setup event handling at the package, or individual task level, as well as select from different events, such as when an error occurs, before or after validation, before or after execution, etc.

Figure 3: Event handling for package errors

Setting Up the Package

Although I utilized the original package for some examples, I created a new example for this article. This package will export three tables from the AdventureWorks database to flat files: Department, Employee, and EmployeePayHistory. When these three files complete successfully in the sequence container, the package continues to the for each loop container, which writes a log through a Script task, ftp's the files to the script, and then deletes the local copies. This didn't work as I expected because I really don't have anything to FTP to, so I get an error as shown below.

Figure 4: Introduction to SSIS Control Flow Package

Because I don't have FTP access, how can we get past this? The first setting I used was to set the MaximumErrorCount to a high number, but it didn't allow the package to keep running. However, there is another useful setting for the FTP task: ForceExecutionResult. ForceExecutionResult allows you to force a certain result for testing purposes, so I could set a failed task to success to see how it will work if we were to get FTP up and running. However, the container fails as well so I had to set the property on that container too. This property works in the converse way; the package can be tested for error handling by setting the result to Failure as well.

It may seem like cheating, but if you have FTP setup, feel free to try it out. I vouch that everything else works, as we shall see. The sequence container setup is straightforward; let's look at how the loop is setup. First, we setup the folder we will look for names that match the data_*.* construct. From this, the full file paths (fully quantified setting) is returned, in the format of c:\ssis\data_whatever.txt.

Figure 5: For each file iteration setup

A variable created in the variable listing stores the current iteration file name. The variable setup below uses the OutputFileName variable to store the value at index 0 (the only index we use for this collection). This means every time through the loop, OutputFileName contains the current path of the file, which the below variables use.

Figure 6: For each file iteration variable setup

The script writes the package details to a log file, which is useful in tracking what actions took place on what files. The script is fairly simplistic; it writes to a log file, creating it if it doesn't exist, of which file ran and the time:

Because some of the tasks may not validate correctly with a variable with an empty value (a warning often appears), it is possible to set the DelayValidation to true, meaning that validation of the value will occur at runtime. Running this package produces a successful result, and the following log entry is recorded:

Conclusion

SSIS can do more than data pushing, which is evident by the control flow package this article developed. It has much more capability to do file processing, or whatever else you need it to do. With all of the tasks available, it is possible to develop very complex SSIS packages for whatever your business need may be.

References

I used Professional SQL Server 2005 Integration Services as a reference for this article, as it contains a concise overview of the SSIS tool.

Downloads

About Brian Mains

Brian Mains is an application developer consultant with Computer Aid Inc. He formerly worked with the Department of Public Welfare. In both places of business, he developed both windows and web applications, small and large, using the latest .NET technologies. In addition, he had spent many hou... View complete profile