MS BI TOOLS

MICROSOFT BUSINESS INTELLIGENCE

Monday, November 29, 2010

SSIS::::Using Conditional Split Transformation

Introduction

   Create a New Project with the name as ExpressionLanguageDemo.Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to ConditionalSplit.dtsx. If you've already created the project, right-click the SSIS Packages folder in Solution Explorer and
click New Package - and rename that package to ConditionalSplit.dtsx.

                          

Drag a Data Flow Task onto the Control Flow and Double click the Data Flow tab to edit. Drag an OLEDB Source onto the Data Flow Environment we can call it as canvas. Double-click the OLE DB Source to open the editor. click the New button to create a new Connection Manager to the Required database.
Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox:

    Select
      ContactID
     ,Title     ,FirstName     ,MiddleName     ,LastName     ,EmailAddress    From Person.Contact
Click OK to close the editor.

On One Condition...
Drag a Conditional Split transformation onto the Data Flow canvas and connect the OLE DB Source Adapter to it:

                                      

Double-click the Conditional Split to open the editor. Expand the NULL Functions folder in the Operations list (upper right). Drag an ISNULL function into the Condition column of the first row in the Conditions grid. Expand the Columns folder in the Values list (upper left). Click and drag the Title column from the list onto the <<expression>> placeholder in the ISNULL expression:
  
The default name for a Condition is "Case n" where n is the row number of the Condition in the Condition grid. Next, drag and drop the ContactID column into the second row's Condition column. Complete the expression so that it reads: ContactID <= 5000. Rename the Outputs to NullTitles and SmallContactIDs respectively:

                     

What we've done here is define a couple of outputs. One of the outputs will contain rows where the Title is NULL and the other will contain the rows where the Title is not NULL and the ContactID is less than or equal to 5000. Why is this? It's because rows with NULL Titles are redirected to the NullTitles output first. The ContactID value of these rows is never evaluated to see if it's less than or equal to 5000. If neither condition applies to a row, that row is sent to the Conditional Split Default Output. This operates a lot like a Switch statement in C# or a Select Case statement in VB, with the Default Output acting like the Else branch. You can adjust the order of condition evaluation using the spinner buttons on the right:

                                                                 
Click OK to close the Conditional Split editor.

Terminate It!
Drag a OLEDB Destination onto the Data Flow task surface. We'll use the Destination to
output the conditional based results. Drag a Data Flow Path from the Conditional Split transformation to the OLE DB Destination. When you do this, you'll be prompted to select an output from the Conditional Split to connect to the OLE DB output:




After selecting an Output, click Ok to close:

The title of the output you selected appears in the label.

Conclusion
 Expressions are used to branch data row flow inside the Data Flow Task with the Conditional Split
 transformation to the Output Results or Input to the other Transformations.

In the next post we will discuss How to use Multicast Transformation.
ThanQ

No comments:

Post a Comment