Getting Started with SQL Server Integration Services
Building Data Flows
The Data Flow tab of the Package Designer is where you specify the details of any Data Flow tasks that you've added on the Control Flow tab. As with Control Flows, you drag and drop things from the Toolbox to build Data Flows. There are three groups of objects that make up Data Flows:
- Data Flow Sources are ways that data gets into the system. These are things like DataReaders, flat files, and XML files
- Data Flow Transformations let you alter and manipulate the data in various ways. Here you can do lookups, joins, sorts, merges, and so on.
- Data Flow Destinations are where the data goes when you're done with it - DataReaders, Recordsets, SQL Server databases, and other locations.
As with the Control Flows, you hook together Data Flows on the designer surface. Figure 4 shows what the Data Flows might look like for a small and simple package. Of course, in the real world you're liable to have a much more complex set of Data Flows than this.
Click here for a larger image.
But Wait, There's More!
SSIS also supports a complete event-handling system. You can attach event handlers to events of tasks or to the package itself. Event handlers are defined on the Event Handlers tab of the Package Designer. When you create an event handler, you handle the event by building an entire secondary SSIS package.
When you work in BIDS, your SSIS package is saved as an XML file (with the extension dtsx) directly in the normal Windows file system. But that's not the only option. Packages can also be saved in the msdb database in SQL Server itself, or in a special area of the file system called the Package Store.
Storing SSIS packages in the Package Store or the msdb database makes it easier to access and manage them from SQL Server's administrative and command-line tools without needing to have any knowledge of the physical layout of the server's hard drive.
BIDS also provides complete facilities for running and debugging SSIS packages, including single-stepping through them so you can see what's going on.
It's a Whole New World
By now, you probably get the idea: SSIS is both more complicated and more powerful than its ancestor DTS. Fortunately, BIDS is a much more powerful tool than the DTS Designer, making it possible to deal with the complexity of SSIS without getting lost in the details. The learning curve can be steep at first, with a large number of tasks available to choose from. But if you stick with it, you'll find that SSIS is an extremely useful tool for database automation tasks of all types.
About the Author
Mike Gunderloy is the Senior Technology Partner for Adaptive Strategy, a Washington State consulting firm. You can read more of Mike's work at his Larkware Web site, or contact him at MikeG1@larkfarm.com.
Page 2 of 2