question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked Monalv-msft edited

Cannot aquire connection from connection manager

Hi,

I am trying to follow an online instruction on to read in some excel files into a db .

http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

I have set everything up as per instruction however i am getting the follwowing error when I try and run it...


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

Error at Data Flow Task [SSIS.Pipeline]: Excel Source failed validation and returned error code 0xC020801C.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

Error at PalletUpload [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)



I have set the variable in the foreach file loops to be a string and have made sure there is an xlsx file in the location.

Can anyone help?

Thanks




sql-server-integration-services
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ChristopherJack-1763,

And the Excel file is not opened in MS Excel?
And you are getting a fully qualified path in the variable?

0 Votes 0 ·

@YitzhakKhabinsky-0887 Thanks for helping..

I tried removing the excel connection manager so it was only the forloop and ran it and I got

Warning: 0x8001C004 at Checks files: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

So it looks like it cant find it...?

I have the filewildcard as *.xls

I changed it to *xlsx and that part works.. i have readded the excel connection it is still failing with the same error when i try and run it

0 Votes 0 ·

76428-new-bitmap-image.jpg



see image - Excel not opened

0 Votes 0 ·
new-bitmap-image.jpg (333.8 KiB)
Show more comments

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @ChristopherJack-1763 ,

Warning: 0x8001C004 at Checks files: The For Each File enumerator is empty.

1.Please check if the excel files exist in the folder that you used in the Foreach Loop Container.

2.Please set example value in the variable and test if we can preview excel data in the Excel Source.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ChristopherJack-1763 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·