Scripting Data Flow in SQL Server Integration Services

When you develop Microsoft SQL Server solutions, you might need to create SQL Server Integration Services (SSIS) packages to retrieve the data you need. In most cases, SSIS packages contain at least one Data Flow task that extracts, transforms, and loads data into the target destination. For each Data Flow task, you must include the components necessary to carry out the data flow operations. However, sometimes the built-in data flow components can’t perform a specific task. In this case, you can often use the Script component to create a custom script that extends your SSIS package.

Getting Started
To illustrate how to create such a solution, let’s use SQL Server 2005’s AdventureWorks sample database. This database’s Product table includes a column named ProductNumber, which contains string values that identify each product. Although the product number values have a similar structure and all identify the product type and category, in some cases the product number value includes the product size. The product type and category, when combined into one value (i.e., the product number), uniquely identifies each product. The ProdNumber column in Table 1 provides examples of product number values as they’re stored in the ProductNumber column in AdventureWorks’ Product table. To complete my example solution, use the SSIS Script component to extract the category identifier (i.e., the fourth through seventh characters) and product size, when applicable, and to identify each product as one of four types: frame, bike, clothes, or miscellaneous. . . .