Let
us assume that we have list of Excel files placed in a folder and that needs to
be extracted and loaded into another database say MS SQL Server. The Excel
files should contain data in common sheet (name).
To
accomplish this task we will use For Each Loop Container to extract the data
from the Excel file placed in the folder.
Below
are the steps that need to be followed to achieve this task through SSIS.
Step 1: Create a new SSIS Package and Rename it to “Extract Data from
Multiple Excel File.dtsx”
Step
2:
Create Connection Manager for Source (Excel Connection Manager). Perform the
following task:
- Right click on
the Connection Managers pane and choose “New Connection…” from
context menu, Select “EXCEL” and click “Add” the “Excel
Connection Manager” window will be opened.
- On Excel
Connection Settings Pane specify any one of the excel file name among
all other files that need to be extracted.
- Check the
checkbox that says “First rows has a column name”
- Rename this
Connection manager as “Excel Connection Manager - Source”.
Fig 1: Folder structure.
The below figure (Fig 2) shows the Data
in Excel sheet of file Australia.xls.
Fig 2: Data layout
Step
3:
Drag and drop “Foreach Loop Container” in Control Flow area. Set the
properties as follows:
- General: Change the Name
to “Foreach Loop Container- Iterate Multiple Excel File”.
- Collection: Set the
“Enumerator” in Collection pane to Foreach File Enumerator. Refer
to Figure 3.
In addition to this make the following
Enumerator Configuration settings:
- Folder: Specify the
Folder path where all the excel files are stored which has to be exported
to the common repository. Here the folder path specified is same as shown
in Figure 1 (address bar).
- Files: Specify the
file name as “*.xls” which will limits to excel files only and will not
go to read any other file type even if they are present in the specified
folder. We can specify "*.*" here, if we are very much sure
that all the files must be of ".xls" type.
- Check the checkbox Traverse subfolders if
you have some Excel files in the subfolder otherwise ignore this.
Fig 3: Foreach File
Enumerator Configuration
Variable
Mappings: Map the Excel file name to a particular variable. So
that on each iteration different files will be assigned to this variable. So,
in drop down click <New Variable…>, a new window “Add variable”
will be open and declare the variable as “FileName”. In “Value”
textbox specify (Fig: 5) the full path of one of the excel file that has to
exported which will be the default value for this variable.
Fig 4: Foreach loop Enumerator Variable Mappings
Fig 5: Add Variable
Please note that variable name is case
sensitive.
And
finally click “OK” to close the Add variable window. Variable Mapping
tab will look like below after configuration. Set the Index of this variable to
0.
Fig
6: Variable Mapping
Click
“OK” to close the “Foreach loop Editor”.
The
added “Foreach Loop Container” Will iterate through each excel files placed in
the folder (the folder path specified on Collection tab above). So, while
iterating a new file name should be assigned to the variable but we have fixed
its value as “Australia.xls”. We need to
make the filename dynamic follow step 4.
Step
4:
The “Excel Connection manager – Source” created in Step2 has to me
modified as we have to iterate through each of the excel files specified in the
folder. To make this connection dynamic go to the Properties Window of the “Excel
Connection Manger – Source” and select “Expression” (shown in figure
13) and on the ellipse button click to open Property Expression Editor. Under "Property"
dropdown choose “ConnectionString” and click the ellipse Button right of
the “Expression” textbox to write the expression for it.
Fig
13: Property Expression
An
Expression Builder window will be open to write the expression. It has 3
sections in it, the first section specifies the Variables which contains the
System as well User defined variable and the second section contains inbuilt
functions. The third section says Expressions where we write the Expression. So
under this textbox write the following the expression for Connectionstring.
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::FileName] +"Extended Properties=\"Excel
8.0;HDR=YES\";"
Click
“Evaluate Expression” button to evaluate the expression written for the
ConnectionString. Click “OK” to close the Expression window.
Fig 14: Expression Builder
Here
the @[User::FileName] is the variable created to hold the name of Excel
file. So on each iteration a new excel file will be assigned and hence it will
hold dynamic value in it.
Step
5:
Now Drag and Drop the Data Flow Task inside the “Foreach Loop
Container- Iterate Multiple Excel File”. Double click Data Flow Task
and will navigate to “Data Flow” tab.
Step
6:
Drag and drop the “Excel Source”. Make the configuration as specified in the
below figure.
Data
access mode-
In Data access mode dropdown select Table name or view name.
Name
of the Excel sheet-
Specify the name of sheet whose data has to be extracted.
Here
in the Excel sheet name we have specified as “Sheet1$” since the data is
present in the Sheet1 of the excel file. We can also say that we are
restricting to extract only those data which is placed in the “Sheet1$”. In any
of the Excel file if “Sheet1” is not present then package will fail.
Fig 15: Excel Source Editor
Click
on the “Columns” tab to check whether the Columns Names specified are
reflected or not. Click “OK” to close this window.
Now
we are going to hold all the data being extracted from Source to another Excel
file. So we have to create a separate Excel Connection manager for the
destination and name it to “Excel Connection Manager”.
Step
7:
Drag and Drop “Excel Destination” on Data Flow. Extend the Green arrow
of “Excel Source” created in Step 9 and attach to the “Excel Destination”. Make
following Configuration:
Data
access mode- Remember here Data
access mode is set to Table or View. Here the data is being sent to a
particular table which will hold the data from various sheets of excel files.
Fig 16: Excel Destination Editor
- Connection
Manager:
Specify the Destination connection manager created in step 10 under OLEDB
Connection Manager.
- Data Access
mode:
Set to Table or View
- Name of the
Excel Sheet:
Click on New to create new table or select existing table from dropdown.
Fig 17: Create Table
- Mappings: Map the Columns
of source to Destination.
So, we have mapped the columns to extract data from
Source to load into the Destination. We can execute the package to check the
data in the destination excel file. There might be the chance of getting
package failure which may be due to data format. So we will track as what are
the Rows which through the error.
Step 8: Execute the package.
Fig 18: Package Execution
Conclusion:
We
have extracted the data from the Excel file from source and placed them to the
excel destination as final data. We can take different destination to hold the
data, only we need to change the destination connection String. So, you can
play with the data stored in the number of excel files and see how it works.