August 26, 2019 at 4:23 pm
Using SSIS VS 2017 to import data from Excel to SQL. I have set up a For Each Loop container with a user variable that will import multiple Excel files where the file names are changing. I can get this far in the process and it works but the next hurdle is that each excel file has a changing sheet name.
Example: 'Sheet1.xls' tab name 'Sheet1'
'Sheet2.xls' tab name 'Sheet2'
The For Each Loop container will execute and process Sheet1.xls but error on Sheet2.xls because the sheet or tab name in Sheet2.xls is different from Sheet1.xls.
Can anyone walk through getting it to work such that SSIS recognizes the change in the sheet name and continues the loop.
Any questions or additional details let me know and thank you in advance
August 26, 2019 at 4:45 pm
This is not a pleasant one to solve.
The only way I know of doing this is to write some C# code in a script task which will get the sheet name from any specified workbook (I assume that these are not multi-sheet workbooks?) and set an SSIS variable to contain the sheet name.
You would then use that variable name as part of an Expression when opening the sheet in your data flow.
Alternatively, your C# code could attempt to rename the sheet to your standard (I've never tried to do this, so cannot be certain it's possible).
August 26, 2019 at 5:30 pm
Thank you for your reply. Yes there is only one sheet per file. I like the idea of just renaming the sheet to a single standard name for each file but as you say is that possible.
August 26, 2019 at 6:39 pm
It is certainly possible, though it is likely you would have to install things on your server which should not really be there (the Microsoft Excel application).
Once you do that, pretty much all Excel functionality becomes available to you in code.
But I wouldn't do it.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy