April 20, 2016 at 10:20 pm
Hello,
I have created a package to import multiple excel files into a SQL Table; however, my dilemma is trying to create an "Excel Source" with different worksheet tab names in each excel file. The data layer is the same for the specific files, but can only specify one sheet name in the Excel Source. Is there a way to make the sheet tab name dynamic in the package? or since it is only one tab for each file; just load the data with out looking at sheet tab names?
Any help would be greatly appreciated.
Thanks in advance!
April 20, 2016 at 10:40 pm
April 20, 2016 at 10:54 pm
You could use an expression in the connection manager to change the name of the sheet. Logic in your SSIS package (perhaps a Foreach loop) would deal changing the value of the expression each time through the loop. This assumes that you know the names of the sheets in advance or can write code to get a list of sheets from each spreadsheet you process.
If you know them in advance, write a SELECT statement that returns the list of sheets. Run the SELECT in a exec sql task and send the result to a variable. Use the variable in the loop (from memory, loop through an ADO Recordset will be what you want).
As for the other option, I haven't ever needed to find the name of all sheets in a spreadsheet but I am pretty sure that there is a way to do it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply