24

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
user1205746
  • 3,110
  • 11
  • 44
  • 73

3 Answers3

33

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 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 – user1205746 Feb 03 '14 at 21:02
  • @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. – ikariw Jul 23 '15 at 15:31
  • 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"? – Stack Overflow Jul 17 '16 at 05:55
  • 1
    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 – Sai Bhasker Raju Dec 09 '16 at 11:21
  • 1
    Option B with the added info on setting it on the Data Flow as well. A sequence container in my case. – frostymarvelous Jan 26 '17 at 16:20
  • In my case, problem still persists.. I still get "[Excel Destination [32]] Error: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database." error. Any ideas?? – rpd May 10 '17 at 10:22
  • @rpd Try asking a formal question on the site with all the details (screen shots are usually helpful) – billinkc May 10 '17 at 13:30
  • Issue resolved. I set DelayValidation property to true in my package level as well and it worked – rpd May 12 '17 at 06:52
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.

Mauro Torres
  • 695
  • 6
  • 6
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\";
iminiki
  • 2,549
  • 12
  • 35
  • 45
Ajeet Verma
  • 1,021
  • 1
  • 7
  • 25
  • 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. – Ubercoder Sep 20 '19 at 14:10