Integration Services upload of Excel file to SQL Server Table Error

Question

Post reply

Integration Services upload of Excel file to SQL Server Table Error

Patricia Monreal

SSC Enthusiast

Points: 142
More actions
December 6, 2007 at 8:45 am

#119859

I often use BIDS (2005) SQL Server Integration Services to upload text and excel files into SQL Server 2005. However, I received an Excel file from an external source, and when I try to upload it, I get the following error:

[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Greg Milner SSCrazy Points: 2425 More actions · Answer 1

Greg Milner

SSCrazy

Points: 2425

December 12, 2007 at 11:47 pm

#759696

Wow. What a coincidence. Today I tried to do the same thing and got the same basic error. I would like to see any responses that come your way. Right now, I am researching this. If I find anything useful I will post it here.

Thanks.

G. Milner

Dave Periam Valued Member Points: 58 More actions · Answer 2

Dave Periam

Valued Member

Points: 58

December 13, 2007 at 8:54 am

#759882

I/We are also experiencing this problem .. and have done for the past two weeks ..

I REALLY hope that a resolution to this issue arrives soon ..

Dave

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 3

Dave Balsillie

SSCertifiable

Points: 5589

December 14, 2007 at 9:43 am

#760341

Have to state some of the obvious points:

Does the file open / load in Excel? Any messages issuse there?

What version of Excel was the file created in? (Office 2007 is showing up more frequently now.)

Hve you tried opening / saving the file to a new file, and retrying your import? Any difference?

Greg Milner SSCrazy Points: 2425 More actions · Answer 4

For me, the file opens in EXCEL just fine. The other thing is when you are creating the Excel source object in SSIS, you can created it and it actually lets you preview the query you have created against the XLS file. It seems to work fine in the design process, in my case. It just fails when you actually try to run the SSIS package, and it fails at the Excel source object, before it moves on to Derived Column Transform or anthing else.

As a workaround, I just had the person supplying the data give it to me in a .csv and that works fine with the Flat File Source. I will probably stick with this because the .csv is less tempting for people to tweak before they give it to you (changing columns, adding worksheets, etc.) and so more consistent over the long haul, I think.

As I like to tell people - especially bean counters: "Are you doing formulas and calculations? No? Then don't use Excel!"

Thanks.

G. Milner

John.Harris Grasshopper Points: 14 More actions · Answer 5

Hi all,