Ssis Read Column in Excel Cell Into Variable

This commodity explores an SSIS package for importing multiple Excel files information into SQL Server tables.

Introduction

Recently I come across a situation in which third-political party vendors send multiple Excel files to load data into SQL Server tables. We can use SSMS Import and Export Wizards for data import. You tin read more than about this in How to import/consign data to SQL Server using the SQL Server Import and Export Wizard.

Suppose you lot received multiple Excel files (let'southward say 50) for information import. You demand to launch an import wizard 50 times and complete information technology. It is a transmission, dull, and time-consuming task.

As I started before in my articles, SSIS is a true friend for DBA and developers. It comes for rescue in such situations. Let'due south explore the SSIS solution in this article.

Sample data

For this article, I want information import from the post-obit Excel files:

Each Excel file contains one row, and source cavalcade shows the excel file proper name:

Prerequisites

• SQL Server Data Tools or Visual Studio 2019
• SQL Server instance

Create an SSIS package for the information import from multiple Excel files

First, we will create an SSIS package for importing a unmarried Excel file data into the SQL Server table. Later, we will convert the same package and import multiple Excel files data using SSIS variables and parameters.

Create a SQL table for information import

Nosotros require a SQL table that will accept information from SSIS import performance. Execute the following script for creating a SQL tabular array for data import:

CREATE Tabular array [ dbo ] . [ SSISDataImport ] ( [ EmpID ] [ int ] Goose egg , [ EmpName ] [ varchar ] ( 50 ) NULL , [ Source ] [ nvarchar ] ( 50 ) NULL ) ON [ PRIMARY ] Get

Add a Source excel file connection

Open up Visual Studio and create a new SSIS package project. In Control Period, right-click and go to New Connection:

It shows all available connection types. Click on EXCEL connection manager and add it:

In Excel Connexion Manager, provide the path of Excel file, and information technology automatically selects the Microsoft Excel versions. The outset row of the Excel sheet contains the column header; therefore, nosotros have a check on the First row has column names option:

Click OK, and it shows the Excel connexion in the connections manager area:

Add a destination OLE DB connection

Now, add a destination OLE DB connection director. Click on add new connection and select an OLE DB connection:

Add together SQL Server example name, hallmark (windows\SQL) in the OLE DB connection manager:

We take both source and destination connections in the connectedness manager window. We volition use these connections in the data period and control flow tasks of the SSIS packet:

Add a information catamenia task for data import from Excel to SQL table

Add a Information Flow Task in the Control Catamenia. This data menses task will flow information from the Excel files into SQL Server tables:

Rename Information Flow Task to Data import from multiple excels files. It is an optional footstep. However, it is a improve approach to renaming the tasks. We tin can hands recognize the chore desired behavior using the custom names:

Double-click on this data menstruation task, and it takes you lot to the Data Flow tab. Elevate an Excel Source to the data catamenia. A red cross icon shows that configuration is required for this SSIS job:

Double-click on this excel source and in Excel Source Editor, select the Excel sheet that contains data. We should have data on similar sheets of all Excel files:

Click on Preview, and we tin get a glimpse of the Excel sheet information:

In the left-hand menu, click on Columns and verify the Excel sheet columns. If we desire excluding any column from information import, we can remove the checkmark from the item cavalcade:

Click on OK, and it shows the successful connexion of an Excel Source:

Now, elevate an OLE DB Destination and connect it with the Excel Source using blue precedence constraint:

Double-click on OLE DB Destination and it opens the OLE DB Destination Editor. Specify the OLE DB connection and SQL table that we created before:

Nosotros need to verify the source and destination mapping. Click on Mappings and verify information technology:

Click OK, and information technology verifies the conversion mistake between Unicode and non-Unicode string information types:

To resolve this, we need a Data Conversion job from the SSIS toolbox. Drag it from SSIS toolbox and connect it with the Excel Source:

Double-click on Data Conversion. It opens the Data Conversion Transformation Editor. Select the Input Cavalcade (Source) and change the data blazon as Unicode_string[D_WSTR]. Nosotros besides alter the EmpName data type as String[DT_STR]:

In the in a higher place screenshot, we also changed the output column allonym. Click OK and add precedence constraint from data conversion job to OLE DB Destination:

Nosotros tin all the same come across a ruby-red cantankerous on the OLE DB Destination task. Information technology requires a configuration with the new columns that nosotros derived from the data conversion.

Open the OLE DB Destination Editor once again and navigate to Mappings. In the Mapping, change the input columns, as shown below:

Click OK. We can encounter that all configuration of the SSIS packet is successful:

Execute the parcel, and it shows successful information import from a single Excel file:

Now, execute the following TRUNCATE Table statement to configure SSIS bundle data import from multiple Excel sheets:

TRUNCATE Table [ SQLShack ] . [ dbo ] . [ SSISDataImport ] ;

Disable SSIS project debugging

Right-click on the SSIS project and get to properties. In the debugging menu, disable the Run64BitRunTime option:

Click Employ and OK to save changes.

Add together variables in SSIS package

Nosotros will employ SQL variables for defining multiple Excel sheets in a for each loop. Right-click in information flow and add variables.

• Directory: It holds the path of all Excel files
• ExcelPath: Information technology holds the path of an Excel file along with file name

Under the Connexion Manager, click on the Excel Connection Managing director and view its properties. Click on Expressions, every bit shown below:

In the Expression Architect, aggrandize the Variables and Parameters binder. Elevate the variable [User:: ExcelPath] to the expression and evaluate the expression. It should bear witness the variable value we divers before:

Click OK, and yous tin can run across the variable in the Belongings Expressions Editor:

Click OK, and information technology shows a symbol "fx" in the Excel Connection Managing director:

Configure a Foreach Loop Container for data import from multiple Excel files

Now, go back to Control Flow and elevate a Foreach Loop Container. Nosotros use this container for starting a loop for the number of executions specified. You tin read more about For Each loop in the Using SSIS ForEach Loop containers to process files in Date Social club and SSIS Foreach Loop vs For Loop Container:

Drag the Data import from multiple excel files chore into the Foreach Loop Container:

Double-click on Foreach Loop Container, and it opens the post-obit loop editor. Brand the following changes:

1. In the Expression, click on eclipse and specify the variable User:: Directory. You can too evaluate an expression, and it should show the value of the variable, equally shown below:

   

2. Under the Binder, specify the path of the folder. We want information import from all files in this folder, then we have specified *.* in the files section:

   

Now, click on Variable Mappings and map the User:: ExcelPath variable as shown below. It automatically takes index value zero:

Click OK, and information technology completes the configuration of SSIS packet for importing multiple files:

Before executing the package, let'south verify that our SQL table does non have any data:

Execute the SSIS packet using the Start button:

SSIS Bundle is successful now. You tin see a green tick icon on the Foreach Loop Container and information import task:

Let's verify data in the SQL table. In the following screenshot, using the source table, we can verify that we take data from all Excel files. We have data from EmployeeData-1 to EmployeeData-10:

We should take a similar cavalcade construction in an Excel file for data import. You can configure additional tasks for error treatment or capture bad data. Take reference of An overview of Error Handling in SSIS packages for information technology.

Conclusion

This article explores the process of importing multiple Excel files into SQL Server table. It saves manual efforts and expedites the data import procedure with Due north number of files. You tin schedule a SQL agent job for SSIS package execution for frequent tasks.

• Author
• Contempo Posts

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over fourteen years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his l-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I take been recognized as the prestigious Best Author of the Yr continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://world wide web.dbblogger.com
I am always interested in new challenges so if you need consulting help, achieve me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

fergusongeoplegatill1940.blogspot.com

Source: https://www.sqlshack.com/import-data-from-multiple-excel-files-in-sql-server-tables-using-an-ssis-package/

Share this post