You need to import data from excel file to sql server table, you can do this with a simple import wizard or simple data flow task in SSIS.
But there are lots of times which you want to do this data transfer dynamically.
Suppose these scenarios:
1- you have many excel files with same structure in a folder, you want to transfer data from these files to sql server tables. but you want to use one data flow not a separate data flow for each of them.
2- you want to create a SSIS package that moves data from an excel file to a sql server table , but structure of data can be different in each time of running the package. first time you have an excel file that has 3 columns of data in sheet1, but second time your excel file has more than 10 columns of data.
3- you have many excel files in a directory with different structures, and you want to import them all in separate tables in sql server. making a Data Flow for each of them in SSIS is very time consuming, you need to find a generic way for data flow.
Let’s start sequentially,
1- you have many excel files with same structure in a folder, you want
to transfer data from these files to sql server tables. but you want to
use one data flow not a separate data flow for each of them.
In this case the only thing you need is one Data Flow task with dynamic source and destination.
SSIS made this easy with combination of package variables and expressions property.
I don’t want to explain how to make a data flow task for excel to sql server . you probably know how to do this already, if you don’t know how to make a simple excel to sql server data flow you can use this link i found it complete tutorial on this case.
Suppose you created Data flow task with Excel Source , and OLE DB Destination , and mapped columns already.
create two variables in package scope of string type, name them as ExcelFilePath and DestinationTableName.
now right click on Excel Connection manager in connection managers area, select properties, in properties window find Expressions. click on (…) button in front of expressions, Property Expressions Editor will open.
in Property select ExcelFilePath, and in Expression select ExcelFilePath variable or type this : @[User::ExcelFilePath]
NOTE: you must set default values for both variables , because if you don’t set them you will give compilation errors at the time of setting expressions.
now your excel connection manager made dynamic by combination of variable and expression, each ExcelFilePath you pass to variable, your excel connection manager will connect to it dynamically at runtime.
Second step is to make Destination Dynamic,
In OLE DB Destination, just double click on it, select Data Access Mode as Table name or View name varaible .
and in variable name select User::DestinationTableName
That’s All.now your data flow made dynamic, at runtime dataflow will connect to ExcelFilePath value, and fetch data and transfer them to DestinationTableName value table.
THE ONLY CONDITION IS THAT YOU MUST HAVE SAME STRUCTURE IN YOUR EXCEL DATA ALL TIMES, BECAUSE COLUMN MAPPING MADE AT DESIGN TIME IN DATA FLOW TASK AND SSIS CAN NOT HANDLE DYNAMIC COLUMN MAPPING.
Now It’s the time for another scenarios:
2- you want to create a SSIS package that moves data from an excel
file to a sql server table , but structure of data can be different in
each time of running the package. first time you have an excel file
that has 3 columns of data in sheet1, but second time your excel file
has more than 10 columns of data.
3- you have many excel files in
a directory with different structures, and you want to import them all in separate tables in sql
server. making a Data Flow for each of them in SSIS is very time
consuming, you need to find a generic way for data flow.
As You read above, you can not use Data Flow task for these cases, because structure of data is different, and data flow can not handle dynamic column mapping.
If your source files was .csv files, I recommend BCP, you can read more about BCP here: BCP.
But in this case we have excel source files.
linked server can be one solution, but I like to use OPENROWSET for this example.
If you don’t know what the OPENROWSET is go here .
a simple structure of insert data from excel file to sql server table with OPENROWSET is:
INSERT INTO yourtable SELECT * FROM OPENROWSET(‘Microsoft.Jet.
‘Excel 8.0;Database=C:\test\xltes
As you see this is a simple SQL statement, so you can do this with an Execute SQL Task in SSIS instead of Data flow task.
just add and execute sql task, set connection. and in SQLStatement type this:
INSERT INTO ? SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=?‘, ‘SELECT * FROM [Customers$]’)
then in parameter mapping tab, map User::DestinationTableName and User::ExcelFilePath sequentially.
for more information about how to map parameters in execute sql task go here .
Now your data transfer is really dynamic, you don’t need to worry about structure of data.
NOTE: in this examples I supposed that name and number of sheets in excel files are static, but you can do them dynamic with another variable for SheetName in OPENROWSET sample.
That’s All.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.