posted 8/24/2010 by kylewalker - Views: [16735]
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.
I think another important take away here is that you can use this for dynamic text sources as well, right?
What is also interesting is that another work around could be to use SSIS file tasks to rename source files (MS Excel or otherwise) to fit the prescribed target source. It's another way of getting the job done, but not as elegant.
Mind you, I have yet to try this as I did the workaround instead, and works wonderfully, until the file rename doesn't work.
I'll have to try this soon enough. Thanks for the good tips!
Yes, you're absolutely right. You can use this same method for a flat file source. I guess I had my blinders on for that one . I haven't actually used the File System Task method either, but it does seem like a viable alternative. Thanks for your input! Much appreciated.
As soon as you are going to set up special informations for the Excel File you might get into trouble as I did.
I painfully found out, that there are some mor thingts to take in account:
The Excel Connection string for example:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ZipCodeExtract.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";
If you run over a lot of files you need to replace the Datasource with a variable like @[User::CM_ImportFileAndPathName]
The Extended Properties are a bit tricky as they have got some "" so I put them also into a variable: @[User::Extended_Properties]
and the Connection Provider: @[User::Con_Provider]
Now all in one like:
@[User::Con_Provider] + "Data Source=" + @[User::CM_ImportFileAndPathName] + ";" + @[User::Extended_Properties]
-----------------------------------------------------------------
@[User::Con_Provider] = Provider=Microsoft.Jet.OLEDB.4.0;
@[User::CM_ImportFileAndPathName] = C:\ZipCodeExtract.xls
@[User::Extended_Properties] = Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";
If you now run a "For each file in... loop container", you are able to apply the correct connection string to your excel file including the additional information how the file should be handled.
I think its also worth mentioning that SSIS is pretty stingy with the meta data so if you try to upload an excel file that has different column names or a different order you're going to run into problems, so make sure your spread sheet layout is consistent when looping over Excel files.
Good post Kyle. I just linked to you for a forum post. It may help Bhavika.