Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

What's new with SSIS?

By Kathi Kellenberger,

Introduction

Maybe the question should be what isn’t new with SQL Server Integration Services. SQL Server Integration Services or SSIS is a completely new product written from the ground up for SQL Server 2005. It replaces DTS, a feature introduced with SQL Server 7 and enhanced with the SQL Server 2000 release. SSIS, like DTS, is a tool to import, export, and transform data. If you haven’t had a chance to play with SSIS yet, you’ll be surprised at how different it is and how much more it can do.

Three dimensional

The first difference I noticed, besides the fact that the work to build packages is done is Visual Studio 2005, is that there are three layers in which to work: Control Flow, Data Flow and Event Handlers.

The Control Flow page is the main area used to build the package. On the Control Flow area you can add many new tasks. Some are containers to hold other tasks such as the For Loop Container. Others perform work like the Execute SQL Task. Some are entirely new like the group of maintenance plan tasks. The one task that is the heart of the Control Flow area is the Data Flow Task. Once you add a Data Flow Task you can drill down to the Data Flow layer. A Data Flow page is created for each Data Flow Task added to the Control Flow page.

On the Data Flow page you set up connections and transforms to import, transform, and export data. Once again, there are some Data Flow Source and Destination objects that you would expect to see and a few surprises, like the XML data source. The next surprise is the number of Data Flow Transformations available. From Aggregate to Fuzzy Lookup to Unpivot, I counted 28 in all. Figure 1 shows a typical Data Flow where some data is imported, a new column is derived, and the results exported to a file.



Figure 1: A typical data flow

Event Handlers is the third of the three layers. It is possible to attach an event handler to any of several events on each task at the Control Flow level. The tasks available in the Event Handlers Toolbox are identical to the Control Flow Toolbox, even the Data Flow task. You can also assign event handlers to a task within an event handler. The good thing is that you have lots of flexibility, the bad thing is that your package could become extremely complex and hard to understand so use caution here. You can use the event handlers to handle errors or check something before a task executes.

More control

SSIS gives you many new ways to control the flow of your package that could only be done before by writing code. One of the coolest control features is the ability to set up looping within the package. Two tasks, the For Loop Container and Foreach Loop Container, are available for this purpose. The For Loop Container (see Figure 2) can be used to repeat a set of tasks a given number of times. The Foreach Loop Container can be used to perform the tasks on each item in a collection, a file in a folder perhaps.



Figure 2: A For Loop Container

The precedence constraints used to connect one task to the next have been enhanced as well. The control flow between tasks may be based on the value of an expression in addition to or instead of the outcome of the previous task (see Figure 3). Again, you have the ability to fine-tune your package with little or no code.



Figure 3: The Precedence Constraint Editor

A package may also be restarted from the task that failed if Checkpoints are enabled in a package. Basically, you configure a file to log information as the package runs (see Figure 4). Then set the FailPackageOnFailure property to True for each task. If your package fails, you correct the problem and restart the package. The package will start up from the point of failure allowing you to save time and resources over starting the package from the beginning.



Figure 4: How to enable Checkpoints

Variables and expressions

System variables for the package and each task are available, and you can set up user variables as well. For example, you need to set up a variable to count the loops when using the For Loop Container. User variables can be set up at the package level or the scope of the variable can be limited to a container or task.

The configuration dialog of each Control Flow task has an Expressions page. Each task property can be controlled dynamically by using expressions. Use variables and the built in functions to build an expression and assign it to a property. This replaces the Dynamic Property Task found in DTS.

Debugging

The ability to step through a package as it runs will save lots of time when troubleshooting errors. Breakpoints can be assigned to events on each Control Flow task allowing you to view the value of variables. Figure 5 shows how to make the For Loop Container break when the loop count equals five.



Figure 5: Breakpoints

In the Data Flow area, Data Viewers may be configured at each step to view the data as it is imported or transformed (see Figure 6). A graph can also be configured as a Data Viewer if that would make more sense. After viewing the data, click the green arrow to resume the package.



Figure 6: A Data Viewer

Easier coding

There are many tasks and transforms that will reduce the need for scripting. Luckily, when you must write some code, the Script Task uses the Microsoft Visual Studio for Applications environment complete with Intellisense to help you navigate the SSIS object model. Variables, as long as they are in scope, may be accessed by the script. To those of you who prefer C#, sorry, only Visual Basic.Net is allowed. Don’t get this task confused with the old ActiveX Script Task still available for converted DTS packages.

Upgrading existing DTS packages

A wizard is provided to convert existing DTS packages to SSIS. Don’t count on this to work 100% of the time, though, because there is not a one-to-one mapping between the DTS and SSIS objects. Many of you have packages that are pretty complex, and you may have had to jump through some hoops to accomplish your goals. The dtsrun command is still supported, and a task specifically for running DTS packages is included in the Control Flow Toolbox. Be sure to read Brian Knight’s article, Upgrading SQL Server 2000 DTS to SSIS for more information.

Conclusions

You will have to spend some time to learn how to use SSIS since it is so different from DTS. SSIS is not an upgrade; it is a completely new product. Once you are accustomed to it, your productivity will increase as you take advantage of all the new tools and tasks.

If you would like to learn more about all the great SSIS features and how to use them, be sure to take a look at Professional SQL Server 2005 Integration Services written by Brian Knight and nine other authors including myself. Brian has made Chapter 5 available for free download if you’d like a sneak peak.

Total article views: 20789 | Views in the last 30 days: 24
 
Related Articles
ARTICLE

Using Event Handlers with Checkpoints (Part 3)

In Part 3 of his series on checkpoints in SSIS, Aaron Akin talks about how you can use event handler...

ARTICLE

Advanced Event Behavior – Level 10 of the Stairway to Integration Services

The tenth article of this series starts to look at more advanced events and how we can use those eve...

FORUM

Controlling Package execution using script task

Controlling Package execution using script task

FORUM

DTS Package, Control name of tab in .xls file

DTS Package, Control name of tab in .xls file

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones