Wednesday, December 10, 2014

For Each loop Package for loading excel files

Here is the simple solution to configure for each loop to parse excel files with different names.
Step by step procedure to create for each loop package in 2012.

Step1:- create new project in the SSDT name it as Foreachlooptest or any name you wish to put
 Stept2:- go solution explorer right click on project name. You will see below screen
Step3:- Go to Configuration Properties ->Debugging->Debug options ->Run64Bitruntime->Make it false 

 Step4:- Right click on control flow ->Variables with Scope current package and data type string Value: source excel full path Ex: - E:\foreach\Source\sourcefile_excel2014.xlsx
Step5:- Drag Foreachloop from SSIS tool Box to control flow.  Variable: - File Path 

Step6:- Double click on Foreachloop Container -> Go to Collections page. Select ->for each File enumerator-> Enumerator configuration browse and select source folder where excel files stored 

Select extension: - xlsx or xlx retrieve file name fully qualified .
Step7: Go to variable mapping add variable 

 Click ok and close it.

Step8:- Drag a DFT into FOREACH CONTAINER and double click on it. You will enter into dataflow section
Step9:- Go to connection manager area and right click on new connection and select Excel Connection click add and edit excel connection see below Images



 Select initial file to load as show above image -> Go to properties of the same excel connection ->
And add expression variable in expression builder Expression: -- Variable (Full File Path)

 And drag an excel source into dataflow and double click on it. Edit and select sheet$ which you want to load and go to columns and click ok
Step10:- drag a oledb destination and create table connect it. Like below show image

 And click debug.
Finish.

SSIS introduction

SSIS (Integration services) is the most powerful ETL tool for data management and analysis included with SQL server Enterprise edition and Standard suit. All though we have many data ware housing tools available in the market (oracle, cognos, sap, informatica, talend, Teradata etc.). Microsoft business intelligence providing efficient tools for managing data and generating reports (SSRS) and for analysis of business data (SSAS). Let me start integrations services first. Firstly you need to know about ETL term.
ETL Stands for
1.       Extraction
2.       Transformation
3.        Loading
Data sources: - As shown in above SQL Server /flat files/third-party apps. Assume a supermarket had hundreds of stores across the state. All the stores sales information stores in central database or else individuals databases. So each super market assumed as source for the data. Here the main task is loading data from the different types of sources to the destination as shown above block diagram. The business owner want to see how much sales has been done per a day or month, year. For this kind of scenario we need to use ETL tools let us go through topic extraction, transformation, loading 
Extraction: - extracting data from any type of source.
Transformation: - Changing the data or transforming the data as per the user requirement.
Loading: - Loading the data into database for analysis purpose. And maintaining historical data in data ware house
Most Of the transformations uses SQL query as input command so SSIS is highly dependent on SQL Server
You must know SQL before learning ssis, ssrs, and ssas