Dynamically assign filename to excel connection string

Question

This is my very first time playing with SSIS in SQL Server 2012. I can successfully read an excel file and load its content to a table in SQL server 2012. The task is a simple direct read excel file then copy to sql server with no validation or transformation for now. The task was successful. But when I tried to make the package read the file name from a variable instead of the original hard coded one, it was generating an error "DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D"

enter image description here

What I did was just replacing the hard coded connection string in the excel connection manager with an expression which took the value of a variable assigned by an expression

enter image description here

The variable was assigned the value before the data flow task started. The variable was checked and did have the correct value.

enter image description here

But the error below was generated when data flow task started.

enter image description here

It would be highly appreciated if someone could point out what I did incorrectly and advise me how to solve the issue.

billinkc · Accepted Answer · 2014-02-03 20:19:15Z

34

Option A

The ConnectionString property for an Excel Connection Manager is not where I go to manipulate the current file, which is contrast to an ordinary Flat File Connection Manager.

Instead, put an expression on the Excel Connection Manager's ExcelFilePath property.

enter image description here

In theory, there should be no difference between ConnectionString and ExcelFilePath except that you will have more "stuff" to build out to get the connection string just right.

Also, be sure you're executing the package in 32 bit mode.

Option B

An alternative that you might be running into is that the design-time value for the Connection String isn't valid once it's running. When the package begins, it verifies that all of the expected resources are available and if they aren't, it fails fast rather than dieing mid load. You can delay this validation until such time as SSIS has to actually access the resource and you do this by setting the DelayValidation property to True. This property exists on everything in SSIS but I would start with setting it on the Excel Connection Manager first. If that still throws the Package Validation Error, try setting the Data Flow's delay validation to true as well.

answered Feb 3, 2014 at 20:19

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

8 Comments

user1205746 Over a year ago

Thank you so much for your solution. Your option B alone works! So basically setting DelayValiday to true will solve the issue! FYI: Your option one does not work though because as you said ConnectionString has more stuff on it than just the path alone. If I set ExcelFilePath, I may have to do some other manipulations on the connection string as well to make it take the content in excelfilepath or it will still be hardcoding as the original or it will bomb if it is blank

ikariw Over a year ago

@user1205746 - updating the ExcelFilePath will automatically overwrite the path in the connection string so it is easier to do that as stated rather than build a connection string yourself. I was doing this but because my file did not exist when the package ran (it was downloaded as an earlier step) I was running into the same problem as you but option B solved it.

Stack Overflow Over a year ago

The both options not works for me, Only to verify, in option B, what is the correct place to set the value -realy in the expression of the ConnectionString into the 'Excel Connection Manager' properties? Should the path format be same as ExcelFilePath like "C:\Projects\Result Files\00000_30-06-2016.xls"?

Sai Bhasker Raju Over a year ago

After setting DelayValidation to True and if it still didnt work , click on the excel task and set ValidateExternalMetaData to false . This will solve that task from throwing error and you can add the data source dynamically

frostymarvelous Over a year ago

Option B with the added info on setting it on the Data Flow as well. A sequence container in my case.

|

Mauro Torres · Accepted Answer · 2018-07-10 23:28:57Z

1

I had a heck of a time trying to get this to work, even after following all the instructions, so I just kept it with a static excel name and added a “File System Task” to copy the file and create a new file with whatever name I need.

answered Jul 10, 2018 at 23:28

Mauro Torres

7716 silver badges7 bronze badges

1 Comment

Ab Bennett Over a year ago

I hear your pain, your way is a ship load simpler

iminiki · Accepted Answer · 2019-09-09 20:07:15Z

1

We can define our connection string like below in Expression:

Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=" + @[User::InputFolder] + "\\"+ @[User::FileName] +";

Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";

edited Sep 9, 2019 at 20:07

iminiki

2,57112 gold badges40 silver badges47 bronze badges

answered Sep 9, 2019 at 18:31

Ajeet Verma

1,1231 gold badge9 silver badges27 bronze badges

1 Comment

Ubercoder Over a year ago

I have been struggling with this exact problem of using an SSIS string variable as the name of the Excel file to load. I tried the previously described approaches using the ExcelFilePath and DelayValidation properties without success. But I can confirm this method of setting the ConnectionString property does work.

Collectives™ on Stack Overflow

Dynamically assign filename to excel connection string

3 Answers 3

Option A

Option B

8 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Option A

Option B

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related