Problem Executing For Each File loop (*.xls)

Question

Post reply

Problem Executing For Each File loop (*.xls)

Theo-929802

Ten Centuries

Points: 1059
More actions
July 2, 2010 at 5:49 am

#147906

Hello people,
I would like your help/experience in the following issue:
I have an SSIS package importing an excel file to a specific table. It executes and does its job normally. Then I try to extend its capabilities by adding a ForEachFile loop. Then I get the following error: excel connection manager failed with error code 0xc0202009
More error detail in the attached screenshot.
Did anybody experience this before? I looked through the internet but could not find any solution.
Thanks in advance!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Theo-929802 Ten Centuries Points: 1059 More actions · Answer 1

I am running on Windows 2003 R2 Standard (x86) and SQL Server 2005 SP2 (9.00.4053)

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 2

Theo....

Pls refer the attached screen image, EXCEL uses 32 bit provider, so set Run64BitRuntime = false of your project properties,(not Solution) to run your package correctly.

and also note, you cant run using DTEXEC command line, instead use x86 DTexec commandline command, if your server is 64 bit, and if you wanted to schedule using commandline,otherwise np

Cheers

RB

Theo-929802 Ten Centuries Points: 1059 More actions · Answer 3

Unfortunately, that's not the case. My server is 32bit and the 64bit Runtime value is already set to false.

Any other solutions?

Koen Verbeeck SSC Guru Points: 259095 More actions · Answer 4

Theo-929802 (7/5/2010)
Unfortunately, that's not the case. My server is 32bit and the 64bit Runtime value is already set to false.
Any other solutions?

I was thinking the same thing. As your package can run solo but not in a for each loop, it has nothing to do with 64-bit.

Can you give more information on how you configured the for each loop?

Realize that you can only use the same Excel Connection Manager if the structure of all the Excel files is identical and if the sheet name is always the same.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Theo-929802 Ten Centuries Points: 1059 More actions · Answer 5

I actually gave up the excel solution.

I switched to .csv and altering the package a bit made it work immediately. 🙂

I gave up finding a solution for the excel.

Koen Verbeeck SSC Guru Points: 259095 More actions · Answer 6

Theo-929802 (7/5/2010)
I actually gave up the excel solution.
I switched to .csv and altering the package a bit made it work immediately. 🙂
I gave up finding a solution for the excel.

Going for .csv instead of .xls is a very smart choice 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

Excel is a pain to deal with in SSIS. You can get so many stupid meaningless errors that it isn't worth doing if there is any way around it.

For example, I once had to process several excel sheets, each containing mixed data types. Unfortunately one month, excel had decided to store most of the numeric types as text, so that meant that the SSIS kept failing. After a very long time searching to find the error, I discovered the change in the data type. The worse thing was, in excel it appeared the format was numeric! But when I manually changed the data types in one of the sheets, it suddenly worked. In the end, I had to write a vb macro that would update particular columns to the correct data type.

Stick with csv when you can 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Theo-929802 Ten Centuries Points: 1059 More actions · Answer 8

Just said to the user that creates the file to save it as .csv through excel. There is no mess with file names and sheet names.

It really made my life easier and the user wasn't affected 🙂

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

Theo-929802 (7/5/2010)
Just said to the user that creates the file to save it as .csv through excel. There is no mess with file names and sheet names.
It really made my life easier and the user wasn't affected 🙂

You may run into problems later, as excel has difficulties with formatting (mentioned above). For example, if you're importing a number that has leading 0s, if the column is formatted as numeric in excel then when converted to .csv you'll lose them.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 10

Theo...

Sounds good that you switched to .csv, if possible can you reply to my post, Just thinking what wud be the actual problem.

1. Are all the input files are having the same structure to import

2. Are you importing all into single excel file or multiple excel files

3. Inside Forloop, I see you are truncating multiple tables ( based on this, guessing you are trying with multiple structured files)

and aslo, I see, your error is not as you stated in origin post text, its "external table is not in the expected format".

Thanks

RB

Theo-929802 Ten Centuries Points: 1059 More actions · Answer 11

Hi there,

1. all files are copies of the same initial file (for testing purposes)

2. do you mean tables? if so yes, to the same table. But the error is before this step, just when it tries to read the file.

3. It truncates some temp tables. Each file is loaded to that temp table and then processed. Truncation occurs every time before importing a file.