How to call Excel data source file dynamically

Question

Hi All,

I have to dump the data from excel files to sql database. Files names like Det_28-02-2012.xls.

For this i have used Data Flow and create connection for excel file (Det_28-02-2012.xls) source (Data row starts from7th row and till BC column Range(A7:BC))  and provide connection string for dynamic pic up as below

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::File] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

I ran the package and it executed fine, but when i deleted the Det_28-02-2012.xls file from source location (SSIS packge will delte the files fro source location once it is uploaded in databases.) , it will show error

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at IBE [IBE Excel Source [1696]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.

Error at IBE [IBE Excel Source [1696]]: Opening a rowset for "Sheet$A7:BC" failed. Check that the object exists in the database.

Error at IBE [SSIS.Pipeline]: "component "IBE Excel Source" (1696)" failed validation and returned validation status "VS_ISBROKEN".

Error at IBE [SSIS.Pipeline]: One or more component failed validation.

Error at IBE: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

 

Kindly suggest on the same, how should i achieve this, as same connection file will not be present for next run.

---

Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/

Answer 1

Hi All,

I have to dump the data from excel files to sql database. Files names like Det_28-02-2012.xls.

For this i have used Data Flow and create connection for excel file (Det_28-02-2012.xls) source (Data row starts from7th row and till BC column Range(A7:BC))  and provide connection string for dynamic pic up as below

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::File] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

I ran the package and it executed fine, but when i deleted the Det_28-02-2012.xls file from source location (SSIS packge will delte the files fro source location once it is uploaded in databases.) , it will show error

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at IBE [IBE Excel Source [1696]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.

Error at IBE [IBE Excel Source [1696]]: Opening a rowset for "Sheet$A7:BC" failed. Check that the object exists in the database.

Error at IBE [SSIS.Pipeline]: "component "IBE Excel Source" (1696)" failed validation and returned validation status "VS_ISBROKEN".

Error at IBE [SSIS.Pipeline]: One or more component failed validation.

Error at IBE: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

Kindly suggest on the same, how should i achieve this, as same connection file will not be present for next run.

---

Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/

Answer 2

You need to put the DelayValidation property of the dataflow to TRUE.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 3

Double post.
Original thread with replies can be found here:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b80213d7-6148-46e5-9437-85e7f975ee58

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 4

Where should i found  DelayValidation property ?

---

Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/

Answer 5

In the properties window of the dataflow task.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 6

check this Link as an example.

the trick to it is to do what Koen had mentioned and on top of that for development, the trick is to pointto a dummy Smaple file, the good thing about sample file is that when ever the package fails and the customer calls for help you can simply tell them to use the sample file in the smaple folder, BUT mainly it really helps in development.

Youmayneed to use IMEX=1, check that as well

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 7

I am having the exact same issue. In my package, I read a set of filenames from a database into a simple foreach loop. I copy the files to an import directory, run them through a dataflow task and delete them.

It runs great in BIDS. When I deploy it, it's a no go. 

The files are getting copied, so I don't think it's a permissions issue, but it can't read the Excel.

I am using Expressions to set the ExcelFilePath on the Excel connection object. This seems to work fine in BIDS but I surmise it is a problem when I deploy to the SQL Server? I've tried the connection string concatenation that is described above and then my package seems to get corrupt and I can't open it until I manually fix the XML file.

Any ideas? Thank you.

Answer 8

Make sure you run the package in 32-bit mode.

Also, can you post the errors?

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 9

Thanks for your reply.

I have set DelayValidation to True on the Foreach loop container and the Data flow task.

My process is Copy The File (this works)->try to read from the copied location (this fails). 

Here is the error when I run it in SSIS. It runs clean in BIDS.

Message

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.


Started:  3:00:01 AM
Error: 2012-03-15 03:00:47.70
   Code: 0xC0202009
   Source: Data Flow Task Excel Timesheet [1]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
End Error
Error: 2012-03-15 03:00:47.71
   Code: 0xC02020E8
   Source: Data Flow Task Excel Timesheet [1]
   Description: Opening a rowset for "Employee$" failed. Check that the object exists in the database.
End Error
Error: 2012-03-15 03:00:49.45
   Code: 0xC004706B
   Source: Data Flow Task SSIS.Pipeline
   Description: "component "Excel Timesheet" (1)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Error: 2012-03-15 03:00:49.46
   Code: 0xC004700C
   Source: Data Flow Task SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2012-03-15 03:00:49.47
   Code: 0xC0024107
   Source: Data Flow Task 
   Description: There were errors during task validation.
End Error

Answer 10

Obvious question here:

there is a sheet named Employee present in the Excel file?

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 11

Yes, it's in the spreadsheet.

My theory is that it isn't actually pointing to the excel file. I am using Expressions to set the ExcelFilePath on the Excel Connection Manager. The Expression is a variable @[User::ExcelCopy] which is a variable which is also set as an Expression @[User::ExcelPath] + "import\\" +  @[User::ExcelFile].

But...on the preceeding step I am performing a Copy File task and using the User::ExcelCopy variable. It moves it just fine to the correct location.

Answer 12

You did set the EvaluateExpression property of the variables to true?

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 13

Yep. It is quite frustrating. I can process 25+ files when I run it interactively, but it fails on the first one in SSIS.

Answer 14

Hi P.Bartelme,

Please refer to the following link about SSIS package failed on the production enviroment:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2c7cad39-9081-48dd-8f16-9cac4ddb827d/

Thanks,
Eileen