Step by Step SSIS–Union ALL Tip # 119

 

Dear All,

In the series of  zero to hero SSIS series this is one of the simplest transformation control which known as UNION ALL. 

As you might aware UNION in general term  which means collective. In SSIS UNION all control is doing the same task.

It collect all the inputs may be of same type of sources or different type of sources and  union them all and provides single output for the same.

Lets understand this by below example.

Suppose, We have different text files which contain fruits &  vegetables name. Now our aim is to combine all the fruits and vegetables name which exists in these files.

if you see below image you will find 3 files which FruitA (Contains fruit names start with letter A) , FruitB (Contain fruit names start with  letter B), FruitP (contains fruit name start with letter P)

Now we need to combine this file using UNION ALL transformation. So follow below step by step

1) Step 1:- Step 1 is simplest step and now you all well versed in this. We need to add a new SSIS package file and drag drop  data flow task .

2) Step 2:- Now add different flat file source for all the 3 files and configure all the 3 files  fruitA.txt, fruitB.txt , fruitP.txt .

3) Once we configured all the 3 flat file sources, We have to drag drop UNION ALL transformation control.Once we drag drop UNION ALL we can provide the output of all the 3 files as a input in UNION ALL control as shown in below  figure.

Step 4:- Now, we have to configure the UNION ALL control. To do this right click on UNION ALL control and click on EDIT option. Here we have to configure all the columns which we need to union.

Step 5:- Once the UNION ALL is configured our next step is to get the output of UNION ALL in a resultant file. for this we have to drag drop destination flat file control as shown in below figure.

Step 6:- Now we have to configure the flat file destination and save it to specific location. A part from this we have to provide input to flat file destination which will be output of UNION ALL control. As shown in below figure I am saving the file on same location and giving name finaloutput.txt

Step 7:- Once all the above step is processed by us the final step is to run the package for this just click on RUN icon or hit F5. If everything is working fine the we will get following  result with all the green right check images.

If you see above image we did union of all the 3 files and saved output in a single file.

Now, for learning purpose and to make the post simplest I use only same type source you can use different type of source and destination as per your need.

A part from this UNION ALL doesn’t remove duplicate so if there are duplicates in the files it will not remove.

I hope this article will help you somewhere.  Please provide your inputs.

Enjoy !!!

RJ!!!

WWH of SSIS Zero to Hero in SSIS series (How to Use SSIS ?) TIP #112

Hello Friends,

Welcome, back to Zero to Hero in SSIS series(Post #110). In last post #111 ,We gone through the WW (What & Why part) of SSIS in this post we will try to understand How to start SSIS ? How to use SSIS tool ?

You can start SSIS with SQL Server Data Tools  which you  can find in Microsoft SQL SERVER 2012 folder in start menu.

Here only the name is different but you will find same Visual Studio IDE when you click on this SQL SERVER data tools icon.

In the IDE when you click on New Project option from File Menu

You will get below screen in which you need to select Business Intelligence template  and then select Integration Services. You will get two option “Integration Service Project” & Integration Service Import Project wizard

Now, We can select any template and proceed further. In general we select “Integration Service Project” . Here I am going to share some basic components when you try to create  SSIS Project.

Don’t bother if you don’t understand the definition of explanation given below. I know theoretically it might be hard but practically it is much much easier. 

1) Control Flow :-  Control Flow  is one of the most important component. Think this as as a container which helps in workflow. If you see below image Control Flow is first tab. It might contain tasks or container. It is helpful in sequencing of task (where task can for loop, send mail, xml process,etc.)  Below is container tools which we can use.

2) Data Flow Task :- Another most important component is Data Flow Task.  As the name state it is a task in which data flow. Isn’t it simple ?  A data flow is part of Control Flow Task. All the major operation can be accomplished with the help of data flow task controls. When you use DFT(Data flow task) you will get various option like Data Sources (from where we need to fetch data) ,Transformation controls( Operation control like aggregation, split etc.) by which we can customize the data and last but not the least Destination in which format we need the data back like SQL Server, MYSQL, ORACLE etc. (We will discuss each DFT controls in detail in coming posts). Below is DFT tools which we will use later on.

3) Parameters:- I am sure you are aware of this parameters. Parameters are variables which help you in execution of your business logic (it might be possible you might require or not require.). Parameter has different scope and according to our need we will use and define the scope. not to worry about this as well right now. We will discuss and see practical use in coming posts. Below is the screen from which we can add parameters if required.

4) Event Handlers:- Event handler is the easy way to have control over your SSIS events. We can have different events like onError, onPostExectution etc. which give us liberty to improve the reliability ,monitoring  and auditing of a package closely. We will surely going to do demo for this.

5) Package :- The final output of all the above core component is a Package. In other word Package is combination of various Control flow, Data flow tasks , parameters to achieve a ETL task. Earlier the extension of package was DTS and with latest version it is DTSX. If someone ask you what you do with SSIS tool you can simply say we create Packages in which we use different control flow & task flow control and once it is completed we deploy the Package. The important point here is we create a Package and then execute it by deploying.

 

Now in broader way if we envision it. Below picture might help you to understand it.

In Next post we will go one step ahead.

Please do provide your inputs what you are thinking so far ?

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 !!!