For current example copy paste the Package.dtsx created in the mentioned tutorial and rename it (e.g. Package Conditional Split.dtsx). The first important change is to leverage the scope to the four Product Categories considered - 'Bikes', 'Components', 'Clothing' and 'Accessories', therefore in the âOLD DB Sourceâ change the query as follows:
SELECT ITM.ProductID ,ITM.ProductName , ITM.ProductNumber ,ITM.ProductModel ,ITM.ProductSubcategory ,ITM.ProductCategory ,ITM.MakeFlag ,ITM.FinishedGoodsFlag ,ITM.Color ,ITM.StandardCost ,ITM.ListPrice ,ITM.Class ,ITM.Style ,ITM.SellStartDate ,ITM.SellEndDate ,ITM.DiscontinuedDate ,ITM.ModifiedDate FROM Production.vProducts ITM WHERE ProductCategory IN ('Bikes', 'Components', 'Clothing', 'Accessories')
From the Toolbox add the Conditional Split component, delete the previous connector and attach it to the new added transformation element. Double click on the Conditional Split in order to access the Conditional Split Transformation Editor in which youâll need to specify the conditions used for the split. Considering as the first condition the case treated in the previous tutorial, define as Condition [ProductCategory]==âBikesâ and rename the default âCase 1â Output Name to âCase Bikesâ. For the second condition consider [ProductCategory] == "Components" as Constraint and âCase Componentsâ as âOutput Nameâ, as in below screenshot:
There is no need to create a third Case for 'Clothing' and 'Accessories', because they will be considered on the Conditional Split Default branch. Eventually you could rename the âConditional Split Default Outputâ Default output name to âCase Elseâ as above.
For each Case in the Conditional Split, including the Default one, youâll need to have a destination, therefore drop two more OLE DB destinations and link to all three destination one green connector from the Conditional Split. For each connector youâll have to select the constraint the branch is supposed to address, and be sure that you selected âCase Bikesâ for the destination inherited from the template package! As for the other two youâll have to create two destination tables using the default structure and rename them [Production].[ClothesAccessories], respectively [Production].[Components]. The two tables will have the same definition as [Production].[Bikes] table. Do not forget to check the âKeep identityâ and âKeep nullsâ checkboxes too!
Save the package and debug it, the resulted package showing similarly with the one from the below screenshot:
For each Case in the Conditional Split, including the Default one, youâll need to have a destination, therefore drop two more OLE DB destinations and link to all three destination one green connector from the Conditional Split. For each connector youâll have to select the constraint the branch is supposed to address, and be sure that you selected âCase Bikesâ for the destination inherited from the template package! As for the other two youâll have to create two destination tables using the default structure and rename them [Production].[ClothesAccessories], respectively [Production].[Components]. The two tables will have the same definition as [Production].[Bikes] table. Do not forget to check the âKeep identityâ and âKeep nullsâ checkboxes too!
Save the package and debug it, the resulted package showing similarly with the one from the below screenshot:
We have used the Conditional Split Transformation and tested it, though we havenât finished yet! As I remarked in the previous post, the data from the destination table(s) needs to be deleted each time the package is run. For this in Control Flow tab modify the Execute SQL Task and in the SQL Statement replace the existing statement with the following lines:
TRUNCATE TABLE [Production].[Bikes] TRUNCATE TABLE [Production].[ClothesAccessories] TRUNCATE TABLE [Production].[Components]
Link the Execution SQL Task connector to the Data Flow Task, assure that the Constraint option value is set to âSuccessâ, save the project, test (debug) the package (twice) and donât forget to validate the output data!
Note:
I preferred to keep things as simple as possible, especially when considering the constraints used for the Conditional Split, however this shouldn't stop you to attempt using the supported library of functions and operators in order to create more complex constraints. This post is dependent on Third Magic Class post and the SSIS âtemplateâ package create in it, so please start with the respective post!