Click here to Skip to main content
Click here to Skip to main content

Using Conditional Split data Transfer in SSIS 2008

, 29 Mar 2010
Rate this:
Please Sign up or sign in to vote.
This article introduces the fundamental concepts behind SQL Server 2008 Integration Services Conditional Split Data Flow Transformation element to transfer data from a set of flat files to SQL Server database tables...

1. Introduction

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

2. Data Load Task

Before we start writing any ETL (Extract, Transform and Load) task, we should first layout a rough sketch of the task and sub tasks involved in the package. In this simple tutorial, we are planning to write an ETL package that does the following:
  • Read a set of text files from a folder, each with a common format
  • Separate the records in the files into two groups
  • Load each group into designated destination table

In this tutorial, we have a set of files that include flower plant names and their types, i.e., Seasonal or Perennial. The files are from different sources but have the same format with a header and data rows. The data elements are comma separated values. The attached compressed file includes a top folder SSISSolution. This folder has two subfolders, i.e., the DataFiles folder containing the two sample data text files and SSISConditionalSplit Visual Studio 2008 solution folder. When you unzip the folder on your local machine, you can open the solution using Visual Studio 2008. However, you will have to update the package for all the connection information based on your SQL Server 2008 installation.

3. Project Setup

Open Visual Studio 2008 IDE and setup an SSIS project as shown in the diagram below. After you name the solution, click OK to create the project. The package will be named as Package.dtsx by default. You may rename the package if you wish.

No Picture Found

4. Add Foreach Loop Container on the Control Flow Panel

Select and drag a Foreach Loop Container control flow element from the toolbox on the left panel of the IDE to the control flow design surface. Rename the element to something meaningful such as "Loop Over Nursery Flower Source". While the control is selected on the design surface, double click on it to open its properties dialog. The dialog is shown in the figure below:

No Picture Found

Next, create a variable to hold the file names iterated in the Foreach Loop Container. Select the "Variable Mappings" on the properties dialog of the loop container and then select the "New Variable" item from the combo drop down as shown in the figure below:

No Image Found

Now, in the new variable dialog shown below, specify the variable container scope as Package and name the variable as vSourceFile, namespace as User and type as String and click ok on both dialogs to return to the control flow surface.

No Image Found

5. Create Flat File Source Connection Manager

In this example, the source of our data are a set of text files in a folder and the destinations are SQL Server 2008 tables. Right click on the connection managers pane on the IDE and choose the "New Flat File Connection...". The Flat File Connection Manager dialog opens as shown in the figure below:

No Image Found

Specify the entries as shown.

  • Connection Manager Name
  • File Name
  • Header row delimiter as Comma{,}
  • Select the check box to use the column names in the first row

All other entries you can leave as default. Note that even if connection will execute within the loop container to use a different file in each loop, you can still specify one file. As you will see below, you need to configure the expression of the flat file connection manager to point to the Foreach Container Loop variable we have already created.

Now, you need to create an expression for the connection string of the Flat File Connection Manager to point to the file variable of the Foreach Loop Container. Select the Flat File Connection Manager you just created and then go the properties pane in the solution explorer and click the "..." button across the expression entry on the properties tree view. This will open the Property Expression Editor dialog as shown below. Select the "Connection String" entry in the left combo box.

No Image Found

Now, click the "..." button for the Expression Builder dialog as shown below. Select the file variable and specify the expression as shown in the dialog and click Ok to close the dialog. This will bind the variable to the connection manager.

No Image Found

6. Create SQL Server Database Destination Connection Manager

Right click the connection manager pane and select "New ADO.Net Connection" entry. On the dialog that opens, click the "New.." button to open the Connection Manager dialog and make selections as shown in the dialog below. Note that the selections of server name and type of connection you specify depends upon the SQL server 2008 you have setup on your local computer.

No Image Found

7. Add a Data Flow Task to the Foreach Loop Container

Drag a Data Flow Task from the Control Flow Items pane to the surface of the Foreach Loop Container. Note that this Data Flow Task is a control flow element and will control the execution of the data flow tasks configured on the Data Flow panel. You may also rename the Data Flow Task to something meaningful like "Split Text Data". The control flow surface with the connection manager will look something as shown in the dialog below:

No Image Found

8. Configure Data Flow

Now, double click the data Flow Task on the loop container. This will select the Data Flow tab panel on the designer and display the "Data Flow Sources", "Data Flow Transformations" and "Data Flow Destinations" on the tool box pane on the left of the designer. Drag a Flat File Source item from the tool box and place on the Data Flow surface. Double click the Flat File Source to open the properties dialog. Select the Flat File Connection manager you have created in this tutorial in the combo box of the dialog.

Now, drag a Conditional Split element from the Data Flow Transformations panel to the Data Flow surface and connect the Flat File Source to the conditional split by dragging the green arrow from the Flat File Source to the Conditional Split. Double click the Conditional Split element to open the dialog as shown below:

No Image Found

On this editor, you will create condition for splitting the data to send to the source. Because the Conditional Split is linked to the source that has the connection to the Flat File Source, you will be able to create condition based on the data. We will create the following two conditions. The fact that we can specify multiple conditions is a powerful visual and computational feature of SSIS. This is extremely useful in reporting and analysis situations.

  • Pick up all perennial flowers
  • Pick up all seasonal flowers

In the dialog shown above, expand the column names node and drag PlantType column to the table rows below and name the condition and output name as shown in the dialog. This completes the Conditional Split specification. Next we will add the destinations for the two filtered datasets.

Drag two ADO NET destinations to the Data Flow panel. Rename the destination to something meaningful. Drag the output green arrows from the Conditional Split to the destinations. As you drag the green arrow to the destination, you will be prompted with the following dialog to specify the filter condition for the destination. The dialog is shown below:

No Image Found

Select the condition you have specified before and click ok. Now, you need to select the destination table. Double click the destination dialog to open the editor. Choose the ADO NET connection manager from the drop down and and the destination table as shown in the dialog below. Optionally, you can go to the Mappings pane on the editor and setup the mappings between the filtered source columns and destination table columns. You can do this by dragging the source column to the mappping table on the lower panel of the editor dialog. Both the views of this destination dialog editor are shown in the figures below:

No Image Found

No Image Found

The final layout of the data flow pane should look similar to the one shown in the figure below:

No Image Found

Now, you can run the package in the Visual Studio environment and verify that the two destination tables are populated with the filtered record sets.

9. Conclusion and Discussion

This simple tutorial demonstrates the usage of conditional split transfomation in SSIS. The sample can be extended to any source and destination with modifications to connections and split conditions. This feature of the SQL Server 2008 provides powerful mechanism for visually designing ETL packages for reporting, analysis and data warehousing scenarios without writing a single line of code.

In subsequent articles in this column of CodeProject, I will cover other advanced SSIS data transformation tools and their usage.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Surath Rath - 2010
Software Developer (Senior)
United States United States
The author has an MS degree in Computer Science and Engineering from the University of Tulsa. He specializes in several Microsoft Technolgy Areas including Desktop and Web application design and development using WPF/Silverlight with PRISM/MEF patterns and practices, WCF Service Development and Data Access Layer Design and Development using Entity Framework and nHibernate. He is also an experienced professional in Application and Solution Architectures and is a Microsoft Certfied Technology Specialist in WCF (.Net 4.0) and .Net 4.0 Applications Foundation. Currently, he resides in Houston. He can be contacted via e-mail surath_rath@yahoo.com.

Comments and Discussions

 
SuggestionA new way PinmemberShaktisampad Dash30-Sep-13 10:11 
QuestionExcel Destination not showing all the available destination columns. Pinmemberkc00422-Feb-13 0:48 
QuestionHi Pinmemberkarna61918-Nov-12 0:28 
GeneralThanks PinmemberMember 923024213-Aug-12 8:25 
Questionfor each loop error PinmemberMember 926602017-Jul-12 0:34 
QuestionSSIS Conditional Split PinmemberAjrKeg25-May-12 3:40 
GeneralLoading File with hetrogenous data into multiple tables Pinmemberchandramohanrc10-May-11 3:41 
GeneralForeach File Enumerator setting part was missing PinmemberReny Mathew10-Nov-10 18:48 
GeneralMy vote of 3 PinmemberReny Mathew10-Nov-10 18:45 
Some steps are missing.
GeneralDataFile Pinmembersalmanzaman1-Apr-10 18:33 
GeneralRe: DataFile PinmemberSurath Rath - 20102-Apr-10 1:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 29 Mar 2010
Article Copyright 2010 by Surath Rath - 2010
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid