Read a Section of an Excel File and Move to Another Excel File

How to read data from multiple Excel files with SQL Server Integration Services

By: | Updated: 2016-02-12 | Comments (29) | Related: More > Integration Services Excel

Problem

I have information in multiple Excel files and all my Excel files are placed in the same folder. I want to create a SQL Server Integration Services (SSIS) Package which can read data from multiple Excel files and load the data into a SQL Server destination tabular array. How tin I reach this using SSIS?

Solution

This tip explains how we tin can read information from multiple Excel files using SSIS and load the information into a SQL Server destination tabular array. Please follow all the steps below to sympathize the solution.

Step 1 - Folder Creation

Delight create a folder named Excel_Exercise on the C:\ Bulldoze, then please create a kid folder named Excel_Source. Let's create three excel files inside the Excel_Source folder. Please refer to the image beneath equally a reference.

Excel Source Folder

We will add data these 3 Excel files and then we will read data from all these Excel files using SSIS.

Step ii - Sample Files

I have already created three empty Excel source files in my previous step. Let'due south add data into sheet1 of each Excel file as shown below.

Excel Data Sample

Step 3 - SQL Server Destination Table

Allow'southward create a destination table in which we will load the data later on reading from all iii Excel files. Delight use the below SQL lawmaking to create the tabular array.

                USE [AdventureWorksDW2008R2] GO CREATE TABLE [dbo].[ImportMultipleExcelFiles]( 	[EMPID] [int] NULL, 	[EMPNAME] [nvarchar](255) NULL ) Go

Stride iv - SSIS Bundle Cosmos

Create a parcel and name it as ImportMultipleExcelFiles, please refer to the image beneath.

Create SSIS Package

Step 5 - SSIS Package Variables

Please create a variable named FileName , scope of this variable is ImportMultipleExcelFiles , Data blazon is String . Please refer to the image below.

Create SSIS Variable

Step 6 - SSIS Foreach Loop Container

Add a Foreach Loop Container in the Command Flow Task, delight refer to the image below.

Add ForEach Loop Container

Step 7 - SSIS Foreach Loop Container Collection

Edit the Foreach Loop Container, in the Drove department change the Enumerator value to "Foreach File Enumerator" . Please refer to the image below.

Edit ForEach Loop Container

We have to modify the Enumerator configuration every bit shown beneath.

Folder: Provide a complete folder path location where all our Excel source files are stored. We take all Excel files stored in C:\Excel_Exercise\Excel_Source.
Files: We need to read the Excel files from our source folder, so please enter *.xls in the Files section, this will make sure our SSIS package will read all bachelor .xls files from the source folder. Hither * indicates that the Excel file proper noun can exist anything, but file extension will be .xls. If nosotros demand to read information from a specific Excel file name then nosotros take to configure it accordingly.
Retrieve File Name: Please select the Fully Qualified radio button. Delight refer to the beneath paradigm for your reference.

Enumerator Configuration

To create variable mappings for the Foreach Loop container, select the "User::FileName" variable and set the Alphabetize value to 0 in the Variable Mappings department. Please refer to the epitome below.

ForEach Loop Container Variable Mapping

Stride 8 - SSIS Data Flow Chore

Add a Information Period Task inside the Foreach Loop Container, please refer to the epitome beneath.

Add Data Flow Taskin SSIS

Right click on the recently added Data Flow task and click on Properties, please refer to image beneath.

Data Flow Task Properties

Please mark the DelayValidation property to True, please refer to the image beneath.

Data Flow Task Delay Validation

Stride 9 - Excel Source in Data Flow Task

Add an Excel Source in the Information Flow Task and create a new connection to any of the Excel source files.

Excel Source Assistance

In my case I used the First_Excel_Souce.xls, please refer to the image below.

Excel Source Folder Path

Open the Excel Source Connection and make sure it is configured as the shown beneath.

Excel Source Editor

Footstep 10 - OLEDB Destination in Data Period Task

Add together an OLE DB Destination task in the Data Flow Task and create a connectedness to the destination database. Select the destination table (ImportMultipleExcelFiles) and map the available input source columns to the bachelor destination columns. Please refer to the prototype below.

Add OLEDB Destination Task

After adding the OLE DB Destination task, your Data Menstruum Chore should look like the image below.

Data Flow Task Source and Destination

Pace 11 - Configure Dynamic Excel Source Connection

Equally of now our Excel source connectedness is fixed to one file named every bit First_Excel_Source.xls. We have to make the Excel connection dynamic then that it can connect to each Excel file in the source folder. To make the Excel source connection dynamic, delight correct click on Excel Source Connection so click on Properties. You lot can refer to the image below.

Excel Connection Properties

Please expand the Expression Backdrop, and so select the Connection String property so click on the expression icon. Please refer to the image below.

Connection Properties Expression

Please copy the beneath code and paste information technology in the expression window.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@[User::FileName]+";Extended Properties=\"Excel viii.0;HDR=YES\";"

Click on the Evaluate Expression button to sure it doesn't throw an error then click on the OK button. Please refer to the below paradigm.

Connection String Expression

Step 12 - Execute SSIS Package

Let's execute the SSIS package, equally yous can meet from the image beneath the SSIS packet was executed successfully.

Execute SSIS Package

Step xiii - Review Final Data

Let's preview the data in the destination table. As you can see from the image beneath, the data from all 3 Excel files is loaded per our requirement.

Data Preview Destination Table

Adjacent Steps

Click here to learn how to read data from the nth row in Excel using SSIS
Excel Export Rows Limitation is one of the outcome in SSRS 2008R2. You can observe the listing of other Excel Export Limitations here
Check out other tips on SQL Server Reporting Services Parameters
Check out all of the SQL Server Business Intelligence Tips and Tricks
Check out my other tips hither

Popular Manufactures

About the writer

MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips

Commodity Last Updated: 2016-02-12

joneslonts1973.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4165/how-to-read-data-from-multiple-excel-files-with-sql-server-integration-services/