Dynamic Excel Sheets load into SQL using SSIS

Question

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

FileName: Z:\Users\darsftp\BDS\GBRTest\PROD File Tracking 02-10.xlsx
FolderPath: Z:\Users\darsftp\BDS\GBRTest
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.

Try this out... stackoverflow.com/questions/4444169/…

manderson
– manderson

2017-03-02 19:16:07 +00:00
Commented Mar 2, 2017 at 19:16 — manderson
– manderson, Commented Mar 2, 2017 at 19:16

Community · Accepted Answer · 2017-05-23 10:29:54Z

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

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Mar 2, 2017 at 21:06

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Syed Jafri Over a year ago

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.

Hadi Over a year ago

@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 Over a year ago

@SyedJafri waiting for your reply :)

Collectives™ on Stack Overflow

Dynamic Excel Sheets load into SQL using SSIS

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related