SQL Server Magazine

Creating the Solution
Some users never do anything more with the Import/Export Wizard than enter settings and run the results. But, as I mentioned in "Making Package Magic," the wizard can also generate packages, so it's a good starting point for building more complex packages for chores such as cleaning data, moving data from one schema to another, or getting your files in the right place for a load. Knowing how to modify existing packages is a valuable skill because it saves you from having to start from scratch every time you need a slightly different package than the one before. To start, let's to load a package into the designer. Then, we'll open the package and see how the settings from the wizard flow into the package. Finally, we'll modify the package to perform a slightly different function than its original function, which is a common approach many people take when building new packages.

You can download the complete sample package from "Making Package Magic" at InstantDoc ID 45092. To open the package, launch the Business Intelligence Development Studio from the SQL Server 2005 Start menu. The designer is a project-driven environment in which you create solutions, which provide a framework for managing all your projects, packages, and other files. Solutions are also the most common way to manage your Reporting Services and Analysis Services projects. Although you can edit packages without creating a solution, we'll want to create a new solution for this example.

To create a new solution, select New, Project from the File menu to bring up the New Project dialog box. To put your project into the solution, type a name and location for the project and click OK. Later, if you want, you can create another project and add it to the solution by right-clicking the solution and selecting Add, New Project.

Now we've created a new solution containing a new package, which will be in the Solution Explorer. Because we want to use a wizard-generated package, we can simply delete the new package that the designer creates. To load the package, right-click the SSIS Packages node in the solution, and select the Add Existing Package menu item. The resulting dialog box lets you load packages from SQL Server, the SSIS package store, or the file system.

Modifying the Package
In "Making Package Magic," we exported some tables from the AdventureWorks sample database and used the wizard to load the flat file. The wizard created the destination database and also did some work in the package to create a table to load into. We also set up source and destination connections and data-flow adapters to move the data from the flat file to the new table.

Figure 5 shows the work-flow portion of the package the wizard created, which includes a SQL Task and a Data Flow Task. The wizard uses the SQL Task to generate the table. The Data Flow Task is what actually loads the data from the flat file to the table. Figure 6 shows the data-flow portion of the wizard-generated package. The Data Flow Task contains a flat-file source adapter and an OLE DB destination adapter. This simple Data Flow Task is about as far as the wizard can take us in terms of starting a package.

But suppose we wanted to filter the customers in some way. For example, say the AdventureWorks people have an overstock of bicycle fenders and they want to target customers in rainy states such as Oregon and Washington. We need a way to filter customers who live in ZIP codes ranging from 97000 to 99000. We first need to delete the data flow between the Source and Destination adapters. Next, add a Data Conversion transform to convert the ZIP code from a string representation to an integer. Then, we can add a conditional split to separate the rainy states from the rest. The Conditional Split transform is the one that actually filters the rainy-state residents from the rest of the AdventureWorks customers. Then, in the Conditional Split transform, enter as the condition the following expression:

[PostalCode as Integer] > 97000 && 
  [PostalCode as Integer] < 99000

This expression states that for all rows that have a PostalCode as Integer column value greater than 97,000 and less than 99,000, the Conditional Split transform will send those rows down the Rainy States output. The rows with customers who live outside the specified ZIP code range will get sent down the Default output. Figure 7 shows the resulting data flow.

You'll notice in Figure 7 that I've added a Row Count transform. A Row Count transform is an easy way to terminate data flows when you're not interested in the results. I've also added two data viewers, which appear as embedded screens in the figure. Data viewers let you view data as it flows from one transform to the next so that you can instantly see how the data is changing. Using the Data Conversion transform, I converted the PostalCode column from a unicode text column to an unsigned 4-byte integer. The problem with this conversion is that not all postal codes are convertible to integers—Canadian postal codes, for example, contain letters. However, for this simple example, the conversion works because I'm interested in the ZIP codes for only Oregon and Washington. The transform will raise an error for rows that contain postal codes with letters in them. To get around those errors, I clicked Configure Error Output and directed the transform to ignore errors and truncations—which is OK because I know my data better than the transform, and I know that I'm not interested in the rows that will cause errors.

Running the Package
When you execute the package, you get something like the view that Figure 7 shows. The data viewers pop up, and you can see all the data flowing from the Data Conversion transform to the Conditional Split transform and from the Conditional Split transform to the destination. Notice that the Data Conversion transform converted postal codes with letters into NULLs. You can also see from the row counts that AdventureWorks has 3146 customers living in Oregon and Washington. We could have made the selection of rainy states simpler by filtering on the StateProvinceID column for IDs 58 and 79, but the results wouldn't allow us the flexibility of further filtering—say, if we later decide to filter to only certain parts of Oregon and Washington or to filter urban versus suburban locations.

A Package of a Different Color
So, there you have it. We used the SSIS designer to load a wizard-created package and modify it to do something better tuned to our needs. You can use this pattern to save yourself some package-creation time, at least until you're more familiar with the designer and how to create your own package from scratch. Get to know the designer—I've only skimmed the surface of its functionality. And as always, please send your feedback to the SQL Server Business Intelligence team at kirk@haselden.org, or post your comments to my blog at http://sqljunkies.com/weblog/knight_reign. We welcome your suggestions for improvements and your success stories.

End of Article

Prev. page     1 [2]     next page -->