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

Data1

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.

DragDropMulticast

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

FileDestination

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.

finalDesign

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.

Final_Result

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

I hope this article might help you somewhere.

Enjoy !!!

RJ !!

Zero to hero in SSIS (SQL SERVER Integration Services) TIP #110

Recently, Many friends of mine are interested in learning SSIS so , I thought to write some blogs which might help them.

I selected following area on which I will write blog on SSIS. It would be great if you also share your inputs.

Step 1: WWH of SSIS & Architecture of SSIS ? (What , Why, How ) 

Once you understand What, Why, How you are curious to know how to create a basic program /package in SSIS. I am sure you might have used this basic SSIS package but you might not aware (Import/Export) in SQL Sever.

Step 2:- How to create my first basic package using SSIS ?

With step 2 you will be more comfortable and confident that you can create package. Once your first package is created the next step which I think  is deployment. How to deploy in real world and use it.

Step 3:- How to deploy SSIS package and different way of deploying ?

Step 4:-  How to create basic data flow task package ?

              With data flow task we will try to understand below common controls as well

                 4.a – Data Conversion

                 4.b -  Conditional Split

                 4.c – Derived column

                 4.d – Lookup

                 4.e – Merge

                 4.f – Merge Join

                 4.g- Multi cast

                 4.h – Row Count

                 4.I – Sort

                 4.j – Union all

                 4.K – Others (remaining)

 

Step 5:-  Understand For Loop Container

Step 6: Understand for each Loop container

Step 7: Sequence  container

Step 8:-  Bulk Insert Task

Step 9:- Script Task

Step 10:- Web service Task

Step 11: XML  Task

Step 12:- File System Task

Step 13:- Execute Process task

Step 14:- WWH variables? How to define application variable ?

Step 15:-  Performance improvement of SSIS Package

Step 16:- Interview questions related to SSIS

I hope with above blog steps those my friends will be benefited.

 I appreciate your inputs as well what else we can include in this series .

Enjoy !!!

RJ !!!              

Learn Simple SQL Server Integration Service example database to text file transformation in 5 minutes

Dear Friends,
Today we are going to create simple SSIS task which we will create a text file output using ETL (Extract Transform Loading) of SSIS.
For this first basic this which we need to know
1) Source Database
2) Output file name & path

Step 1:- Open SQL Server Business Intelligence Development Studio (BIDS) and create a new Integration project as shown in below fig.


Step 2:- Now drag and drop Data Flow Task from control flow item’s toolbar as shown in below fig.

Step3:- Once you done with this just double click on Data flow task and you will get a new screen for data flow. Now we have to choose data source for our ETL process for this just drag drop data source from data flow source tool bar.
Step 4:- configure source database. In this example we are using OLEDB data source and SQL server Database. You can choose other database source too.  To configure OLEDB data source just right click the OLEDB data source and click on Edit.


You will get OLEDB Source Editor as shown in fig.
Just click on new button as highlighted in above fig set database. Once database source is done we can choose any data mode like table or view, SQL Command as shown in fig.

I am choosing table employees of North wind database here.  After doing this just choose desire column which you want to export in file. You can choose this column by selecting columns option.

Step 5:- Now Drag drop destination control as we require flat file destination and configure it. We can configure destination same as we did for data source. Just right click on flat file destination control and edit it.


Step6: – Once you done with configuration of file click on mapping and map the columns. As shown in below fig.

Step 7:- Now all set just run using”F5” 

If everything is green then our SSIS package is done successfully.
In next session we will learn how to deploy it, how to use configuration for it.
Till than enjoy SSIS.

Thanks
Rajat Jaiswal