Programming Samples

Click here to go to:

Excel VBA
MS Access
T-SQL
SSIS
SSRS
Crystal Reports
C# Code
ASP .NET Code
Oracle PL/SQL

Back to Home Page

SSIS Conditional Split and Multicast

SSIS - Tranformations

This article demonstrates usage of a conditional split transformation task to separate "valid" data from "invalid" data and usage of multicast tranformation task to send the "valid" data to various files in different aggregated and non-aggregated formats. The AdventureWorks database contains the sample data used in this article.

SSIS Project in BIDS

Begin by creating a new SSIS Project in Visual Studio.

SSIS Project

SSIS Connection Setup

Add a SQL Server Connection by Right Clicking the Connections Manager in the Control Tab and selecting OLE DB Connection. Select an existing connection to the AdventureWorks database, or click on New to create a new one in the Connection Manager.

SSIS SQL Connection

Drag and Drop a Data Flow Task on the Control Tab, then click on the Data Flow tab.

Drag and drop an OLE DB Data Source on the Data Flow tab, right click and select Edit. Select the SQL Database connection and the table, in this case it is the View dbo.vw_SalesAddresses. For additional information on SQL Views, click here: Link to SQL Views.

SSIS View SQL Connection

SSIS Conditional Split Task

Drag and drop a Conditional Split to the Data Flow tab and connect the precedence constraint from the OLE DB Source to it.

Conditional Split

Right click and edit the Conditional Split. In this case, the data is being separated for 2 conditions: one if the data is missing a State or City and one if the data has both the City and State. Notice the syntax in the Condition boxes next to each Output.

Split Edit

Drag and Drop a Flat File Destination to send the data with missing City or State from the Conditional Split. By connecting the precedence constraint from the Conditional Split to the Flat File Destination, a pop up dialog box will request that you select which output you want sent to the destination file.

Data to Flat File

Right click on the Flat File Destination to configure a connection manager.

Flat File Connection Manager

SSIS Multicast Task Addition

The good data will be further transformed into multiple locations. Drag and drop a Multicast Transformation Task on the Data Flow tab. Connect the precedence constraint from the Conditional Split to the Multicase Task and select Clean Data (the valid data) from the Output drop down box in the pop up dialog.

Multitask

Drag and drop an Aggregate Transformation to the Data Flow. This will summarize the Sales YTD by StateProv.

Aggregate Transform

SSIS Excel Destination Files for Multicast Task

Drag and Drop 2 Excel Destinations to the Data Flow. One will be used to capture data from the Aggregate and the other will be used to capture the sales person names, addresses, etc. directly from the Multicast Task Transformation. Drag the precedence constraints from the Aggregate to one of the Excel Destinations and a precedence constraint from the Multicast Task to the other Excel Destination. Configure a Connection Manager for each of the workbooks by Right Clicking and selecting Edit.

Excel Destination

Select a worksheet or click New and create a new worksheet in the workbook.

Excel New Worksheet

Configure the Excel Destination by picking the connection and sheet.

Excel Select Worksheet

Map the columns of each of the worksheet and click OK.

Excel Mapping

The final SSIS package

SSIS Package

Run the package and validate the data.

SSIS Test

Open the Excel files and verify that the information successfully transferred.

Aggregate Data

Excel Aggregate Data

Multicast Sales Data

Excel Multicast Sales Data

Using Multitask Transformations and Conditional Split Transformations are useful in parsing out data without the use of SQL programming language.