Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Setting up an SSIS package with a dymanic Excel Source

  • 24 August 2010
  • Author: kylewalker
  • Number of views: 113925

Just the other day, we had a forum post here on BIDN that was asking how to set up an SSIS package with a dynamic Excel source.  This source can be stored in a different file path or even with a different name on any given day.  A solution for this scenario would be to use an Excel Connection Manager with an expression that uses a predefined variable.  Then, using the dtexec utility, pass the path of the new file to the package as a variable.  The steps to go about creating such a package are as follows:

First, you'll want to create the variable that you'll be using.  For this example, I have called mine "ExcelSource".  Also, make sure you score your variable to the package and not the data flow.

Variable List 

Once you've got your variable all set up, go ahead and create an Excel Connection Manager by right-clicking in the Connection Managers section and select "New Connection..." and the "Excel".

Excel Connection Manager

You can use the Excel file that's available at that time to set up the connection manager.  Today, the file available is ZipCodeExtract.xlsx on the C:\ drive.

Excel Connection Manager 2

Now that you've got your Connection Manager set up, right-click on it and select the Properties (or left-click and hit F4).  In the Properties tab, select the ellipsis next to "Expressions".

Excel Connection Properties

A Property Expressions Editor will pop up.  There you'll select "Excel File Path" under "Property", and then click the ellipsis under "Expression".

Property Expression Editor

In the Expression Builder, simply select the user variable you first created.

Expression Builder

The variable, Connection Manager, and Expression on the Connection Manager are now set.  All that's left is to create the actual data flow.  Pull over an Excel Source inside of a Data Flow Task and configure it to your Connection Manager, and then connect it to your OLE DB Destination and you're all done.  Save this off and you're good to go.  Now, when you execute this package with the dtexec utility, you can pass the new file path in with no problem.

I hope this was, in some way, helpful.

Rate this article:


Other posts by kylewalker

Please login or register to post comments.