Sunday, October 18, 2015

Step by Step SSIS Multicast Transformation TIP #121

Dear Friends,

This is another post in the series of step by step SSIS ,Zero to hero series. In this post we will discuss another transformation control which is Multicast transformation.

Multicast is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say multicast transformation create multiple pipelines for the same data.

Lets understand this by an example. Suppose, we are product developer and selling product in different state in India. Now ,the problem is same product may have different selling price due to different tax system. So, we have to use same data of product but there will be different discount according to sate tax.

Now, here we go step by step. for your information in this example we are going to use similar example which we did in last post of Derived column Transformation tip #120

Step 1:- Add a package and drag drop data flow task and configure source database. I am taking Adventurework2012 database and using product &  productCategory tables for source data query below is simple snap of data


Step 2:- Once the data is configured. Drag drop Multicast control  as shown in below figure and give output of source object to Multicast and try to configure it.


Step 3:- Now, when we have configured Multicast we can get multiple output from Multicast control. Suppose , One state is taking 10% WAT tax, another state taking 20% WAT tax and last one is taking no tax (wow that’s great). So we can drag drop derived column and configure then according to 10% ,20% WAT tax for reference you can take a look of tip #120 

I configured the the 3 different output in same way and adding the respective flat files. As shown in below figure you will see we added 3 files

1) with 10% ,with 20 % and without and tax


After configuration you will get structure something like as shown in below image. As a result we will get 3 flat files with 10% ,20% and without any change.


Step 4:- If you face any difficulty in Derived column configuration and moving the data to file please follow earlier post. Now once everything is configured run the package by pressing F5 or hitting run button you will get following screen. So , if you see Multicast providing similar row count to different output which is 295 rows.


We , can use this Multicast where we need multiple copy of same source data.

I hope this article might help you somewhere.

Enjoy !!!

RJ !!

Step by Step SSIS–Derived Column Transformation TIP # 120

Dear Friends,

In the series of Step by Step SSIS , Zero to Hero in SSIS this is another post in which we will try to understand what is Derived Column Transformation.

Let’s understand this by a real world example. Suppose, we are a shop keeper and our work is buy things from carpenter and sell them by adding our 20%  margin.

So , carpenters give us source data (file, sql server etc) with their rate according to product. What we do actually ,we add another column in our register by adding 20% which is our selling price. So ,our selling price column is a derived column here which we derived from source by adding some of our operations.

Isn’t it simple ?

Let’s understand this now step by step. I am using Adventureworks2012 database here as a source database and using product & ProductCategory table combination query. So , Assume this source data is data of carpenter’s products.  Here if you see below image we have productId, Product Name, color,Category & ListPrice (price at which we (Shop Keeper bought the the product)


Step 1:- It is useless to say here add a new Package in your project. Drag drop data flow task on canvas. Now double click the data flow task you will get a new screen where you need to drag drop source Assistance.

Now,need to configure source assistance where database will be adveturework2012.  If you see below we have same columns which we shared earlier in figure


Step 2:- I am sure you will not face any problem in configuration of source control. Now in step 2 we have to drag drop Derived column.  Once you drag drop derived column you need to provide output of source assistance to Derived Column Transformation. Now to configure this use context menu by right clicking and choose Edit option you will get below screen. Here we have different functions and operators which we can be utilized with columns and variables and parameters to create derived column.


If you see above highlighted row in image we can add a new column or can replace any existing column as shown in drop down.

Step 3:- If you see there is expression column also where we can add custom expression. Here we are multiplying 0.20 in list price and adding it again in Listprice and aliasing this new column SellingRate as shown in below figure


if you see below screenshot we can add other functions ,operators also.


Step 4:- I hope above steps are pretty much clear. Now add a destination file in which we will get derived column “SellingRate” . We have to configured the derived column. As shown in below figure we configured derived column.


Step 5:- Now, Once the destination file is configured run the package by pressing F5 or clicking run option. If everything working fine then we will get all the green checkbox sign as shown below figure


I hope this post might help you to understand the derived column.

Enjoy !!!