Problems using SSIS ForEach container with Excel

Question

Post reply

Problems using SSIS ForEach container with Excel

Mike Jones-349141

SSC Veteran

Points: 287
More actions
July 18, 2006 at 5:07 am

#113727

I am trying to use a ForEach container to read a series of spreadsheets. Keeping it simple initially, I have tested a simple load from a single 2-column spreadsheet into a table - this works. I then placed it in a ForEach loop to read 2 spreadsheets, both of the same format (using the instructions for text files in the tutorial) and get the following message:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Test Loop [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.
Error at Test Loop [DTS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error at Test Loop [DTS.Pipeline]: One or more component failed validation.
Error at Test Loop: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Should I be doing things differently for Excel?

Viewing 9 posts - 1 through 8 (of 8 total)

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

cyriltra SSC Journeyman Points: 77 More actions · Answer 1

Hello,

i've got exactly the same issue

someone could help us please ?

Mike Jones-349141 SSC Veteran Points: 287 More actions · Answer 2

Hi Cyriltra,

I think I've fixed it now.

What I did was set up the initial Excel load using the Import/Export wizard (don't know if this is relevant but it was in response to some advice I found on the web).

I then followed the advice given in the following link:

http://msdn2.microsoft.com/en-us/library/ms345182.aspx

... and ended up with an expression looking something like the following:

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

Hope this helps - if it's not clear, please let me know.

Regards,

Mike

Mike Jones-349141 SSC Veteran Points: 287 More actions · Answer 3

Cyril -

I've just remembered why I used the Import/Export wizard - this seemed to be the only way I could get it to read from a named range in the spreadsheet (as opposed to just reading from a worksheet).

Regards,

Mike

Stefaans SSC-Addicted Points: 488 More actions · Answer 4

First off.. The screen dumps did NOT copy.. Email me and I shall send you the Word document with the screen dump. Steve njm870t@hotmail.com

Iterating through a FOR Each Container to load Excel Spread Sheets

O.K. Folks this is how it is done!!! After many hours of sweat!!

I started with a Data Flow Task that worked stand alone.

I then added a FOR EACH loop container, and placed the data flow into the container.

Note that I also use my ORIGINAL “Incoming Financial Data” Excel Connection Manager.

Here is the trick. I got it from http://www.mydatabasesupport.com/forums/sqlserver-dts/338668-sql-2005-ssis-excel-connection-error.html

“As you're setting the connection from a variable, you just need to

make sure that the variable default value points to this template

file.”

Now that my Data Flow Task is in the For Each container, it is time to open the container and to configure the package variable.

On the collection page

On the collection page choose the folder where your excel spreadsheets will be located.

Make sure that you set the files to *.xls

Keep the Enumerator as Foreach File Enumerator.

We now move on to the Variable Mappings and this is most important

Here is where the “tricky” come in

Click on the variable tab, then click in the variable drop down box.

Choose

The add variable dialog box comes up see above.

Note that I have entered the name of my variable EXCELFILE BUT MORE IMPORTANTLY I HAVE PLACED A DEFAULT VALUE in the Value: box.

Click OK to create the variable and close out of the FOR EACH container.

Now go to you “Incoming Financial Data” EXCEL Connection Manager” , right click on it and select properties.

Find the expressions property in th eproperty dialog box on the bottom right of your screen and click on the ellipse,

The Property Expression Editor will then come up.

Choose connection string and copy the following expression into the box (see above)

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

Click OK

This worked for me ..

Regu SSC Enthusiast Points: 102 More actions · Answer 5

In SQL2008, I am trying to upload data from multiple excel file into database. I get the followng error. what to do?

TITLE: Microsoft Visual Studio

------------------------------

Error at Data Flow Component [Excel Source [18]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Component [Excel Source [18]]: Opening a rowset for "D:\SampleXLS\ChangeLog 20080827.xls" failed. Check that the object exists in the database.

------------------------------

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

BUTTONS:

OK

------------------------------

Stefaans SSC-Addicted Points: 488 More actions · Answer 6

Hi

From the error message, I suspect that Visual Studio is having problems accessing your spreadsheets.

1) Are you using Visual Studio 2005 or 2008.

If you go to my website infogoldusa.com and click on the Presentations tab, you will see the zipped code for my PASS 2009 presentation entitled SSIS and the modern financial institution CODE.

Inside this you will see a Visual Studio Project entitled FASB. If you open this you will find a working model that you CAN use as a baseline.

Should you wish to take this one off line you can contact me at njm870t@hotmail.com.

sincerest regards

Steve

craig.goodman Valued Member Points: 71 More actions · Answer 7

craig.goodman

Valued Member

Points: 71

December 9, 2009 at 5:52 pm

#1090294

This thread has been very helpful. Thank you.

dasbootmonkey Grasshopper Points: 17 More actions · Answer 8

It's much easier if you use the ExcelFilePath property in your expression rather than the ConnectionString property. Then you don't have to mess with building your connection string manually. There are also a lot of other properties availabile to generate your Excel connection string.