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 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.
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 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.
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.
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.
Right click on the Flat File Destination to configure a 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.
Drag and drop an Aggregate Transformation to the Data Flow. This will summarize the Sales YTD by StateProv.
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.
Select a worksheet or click New and create a new worksheet in the workbook.
Configure the Excel Destination by picking the connection and sheet.
Map the columns of each of the worksheet and click OK.
The final SSIS package
Run the package and validate the data.
Open the Excel files and verify that the information successfully transferred.
Aggregate Data
Multicast Sales Data
Using Multitask Transformations and Conditional Split Transformations are useful in parsing out data without the use of SQL programming language.