2

I'm trying to load multiple excel format files (.xlsx) into sql. I have set up my package as followed. My excel files name and sheet name will change daily

File name: PROD File Tracking 02-10.xlsx - Month and Day change daily
Sheet name: 2-10$ -- month and day change daily

Package Structure

For each Loop Container -> Data Flow Task -> Excel Source -> OLE DB Destination

Variables Values

  1. FileName: Z:\Users\darsftp\BDS\GBRTest\PROD File Tracking 02-10.xlsx
  2. FolderPath: Z:\Users\darsftp\BDS\GBRTest
  3. ExtProperties: "Excel 12.0;HDR=Yes"

Need Help

To dynamically pick up file daily with a dynamic sheet name.

I know how to pick up files with a dynamic file name but not with a dynamic sheet name. That's where I'm having the issue.

Community
  • 1
  • 1
Syed Jafri
  • 51
  • 1
  • 4
  • 1
    Try this out... http://stackoverflow.com/questions/4444169/import-data-from-excel-using-ssis-without-knowing-sheet-name – manderson Mar 02 '17 at 19:16

1 Answers1

0

Create a new variable Sheetname and Set it's property Evaluate as expression to True and use the following expression:

REPLACE(SUBSTRING(@[User::Filename],FINDSTRING(@[User::Filename],"PROD File Tracking", 1 ) + LEN("PROD File Tracking"),100),".xlsx","") + "$"

So if your Filename variable value its Z:\Users\darsftp\BDS\GBRTest\PROD File Tracking 02-10.xlsx so Sheetname variable value will be 02-10$ wich is the sheetname.

And in your Excel Source read the sheetname from variable Sheetname

Side note: Excel sheetname always end with $ sign (it doesn't appear in excel)

Read more about variable and expressions in this article

EDIT 1

You have to add an expression the the Excel connection manager connection string property:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::Filename] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

Set DelayValidation property of Data Flow task to True.

Usefull Links

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thank you for your response but I'm getting this error when I try that: Exception from HRESULT: 0xC02020E8 Error at BDSEntityErrorIndicator [Connection manager "Excel Connection Manager"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager. – Syed Jafri Mar 07 '17 at 20:37
  • @SyedJafri This error is not from the sheet name. It is fron the connectionstring expression you are using. You said `I know how to pick up files with a dynamic file name` so my answer was on how to set sheet name dynamically – Hadi Mar 07 '17 at 21:00
  • @SyedJafri waiting for your reply :) – Hadi Mar 16 '17 at 21:51