Alternative of multicast transformation in SSIS

Multicast transformation is useful when we need to make many copies of same data or we need to move the same data to different destination. It has one input and many outputs. The Multicast transformation distributes its input to one or more outputs.

Read More here:

https://bageshkumarbagi-msbi.blogspot.com/2015/02/multicast-transformation.html

We are getting the excel having data of multiple table value. We need to read the excel and store the data into the multiple table based on the columns.

Below is the excel. 

Each row having information about the below

o   Product category

o   Product sub category

o   Product

Below are the columns in Excel files  

We will read the excel file and store the data into the staging table. After load we using the SQL script to load the data into the different table.

In the previous post we saw that how we are using the multicast transformation to load the data in different table.

https://bageshkumarbagi-msbi.blogspot.com/2021/12/use-of-multicast-transformation-in-ssis.html

Alternative of the Multicast is that we need to load the data into Staging table and we load the data from stage to mast table.

Below is the staging table. 

To loading the excel data to staging 

After loading the data we are taking the Execute SQL Task and writing the below script to load the data into the different tables.

insert into DimProductCategory ( ProductCategoryKey ,ProductCategoryAlternateKey ,EnglishProductCategoryName ,SpanishProductCategoryName ,FrenchProductCategoryName ) select Distinct DimProductCategory_ProductCategoryKey ,DimProductCategory_ProductCategoryAlternateKey ,DimProductCategory_EnglishProductCategoryName ,DimProductCategory_SpanishProductCategoryName ,DimProductCategory_FrenchProductCategoryName From product_deatils   insert into DimProductSubcategory ( ProductSubcategoryKey ,ProductSubcategoryAlternateKey ,EnglishProductSubcategoryName ,SpanishProductSubcategoryName ,FrenchProductSubcategoryName ,ProductCategoryKey ) select Distinct DimProductSubcategory_ProductSubcategoryKey ,DimProductSubcategory_ProductSubcategoryAlternateKey ,DimProductSubcategory_EnglishProductSubcategoryName ,DimProductSubcategory_SpanishProductSubcategoryName ,DimProductSubcategory_FrenchProductSubcategoryName ,DimProductSubcategory_ProductCategoryKey From product_deatils   insert into DimProduct ( ProductKey ,ProductAlternateKey ,ProductSubcategoryKey ,WeightUnitMeasureCode ,SizeUnitMeasureCode ,EnglishProductName ,SpanishProductName ,FrenchProductName ,StandardCost ,FinishedGoodsFlag ,Color ,SafetyStockLevel ,ReorderPoint ,ListPrice ,Size ,SizeRange ,Weight ,DaysToManufacture ,ProductLine ,DealerPrice ,Class ,Style ,ModelName ,EnglishDescription ,Status ) select DimProduct_ProductKey ,DimProduct_ProductAlternateKey ,DimProduct_ProductSubcategoryKey ,DimProduct_WeightUnitMeasureCode ,DimProduct_SizeUnitMeasureCode ,DimProduct_EnglishProductName ,DimProduct_SpanishProductName ,DimProduct_FrenchProductName ,DimProduct_StandardCost ,DimProduct_FinishedGoodsFlag ,DimProduct_Color ,DimProduct_SafetyStockLevel ,DimProduct_ReorderPoint ,DimProduct_ListPrice ,DimProduct_Size ,DimProduct_SizeRange ,DimProduct_Weight ,DimProduct_DaysToManufacture ,DimProduct_ProductLine ,DimProduct_DealerPrice ,DimProduct_Class ,DimProduct_Style ,DimProduct_ModelName ,DimProduct_EnglishDescription ,DimProduct_Status from product_deatils

 Execute SQL Task  

Now the package is ready to run.

Records in stage table.  

Records in master table.  

Running the package now.  

Records in the Staging table.

 

Records in the Master tables.  

Data Loaded successfully.