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.
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".
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.
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".
A Property Expressions Editor will pop up. There you'll select "Excel File Path" under "Property", and then click the ellipsis under "Expression".
In the Expression Builder, simply select the user variable you first created.
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.