Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Database, SSIS and other Sql server development

Archive · View All
April 2012 1
May 2012 1

Karunesh Kumar Maurya

Extracting Data from Multiple Excel Files Placed in a folder through SSIS

May 4 2012 12:00AM by Karunesh Kumar Maurya   

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 modeRemember 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.

Tags: 


Karunesh Kumar Maurya
575 · 0% · 64
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • I am extracting data from 5 Excel files using ForeachloopContainer.. and i run the package it showing 50% complete with following error..

    after that I check the table data also it showing only one Excel sheet data only...

    please ensure following..

    [Excel Source [1]] Warning: The external columns for component "Excel Source" (1) are out of synchronization with the data source columns. The column "F1" needs to be added to the external columns.

    The external column "SafetyStockLevel" (29) needs to be removed from the external columns. The external column "Color" (26) needs to be removed from the external columns. The external column "MakeFlag" (23) needs to be removed from the external columns. The external column "ProductNumber" (20) needs to be removed from the external columns. The external column "Name" (17) needs to be removed from the external columns. The external column "ProductID" (14) needs to be removed from the external columns.

    Error: There were errors during task validation.

    commented on Aug 10 2012 4:05AM
    mohankatchur
    1113 · 0% · 23
  • hi karunesh,

    there are total 9 excel file in my folder and trying to extract them using for each loop container the table data also it showing only one Excel sheet data only...

    with following errors

    [Excel Source [1]] Error: SSIS Error Code DTSEOLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    [Excel Source [1]] Error: Opening a rowset for "SALES$" failed. Check that the object exists in the database.

    [SSIS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

    [SSIS.Pipeline] Error: One or more component failed validation.

    Error: There were errors during task validation.

    Please Do reply karunesh asap

    commented on Sep 6 2012 1:10PM
    ss53217
    2838 · 0% · 3
  • hi Karunesh,

    I have also face the same above problem,so please let me know the resolution.

    Thanks, Rohit

    commented on May 7 2014 12:56AM
    monk09876
    3063 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Extracting Data from Multiple Excel Files Placed in a folder through SSIS" rated 5 out of 5 by 3 readers
Extracting Data from Multiple Excel Files Placed in a folder through SSIS , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]