SSIS OLEDB Connection issues with acquire connection error

3

Thank you for looking into this. I am fairly new to SSIS and got stuck. I have googled a lot of resources but dont see anybody else having the same issue.

I have created an SSIS package which downloads a 'abc.zip' file from a website. I am unzipping the file using 'Execute Process Task', the unzipped file is in 'abc.DBF' format. I am importing the data from the 'abc.dbf' into sql server 2008R2. The package runs fine and exactly as intended when I run it in BIDS without config file, but when I create a config file for the package it fails at the Connection at 'DBF Source' in the Data Flow Task with the errors mentioned below:

enter image description here

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

[SSIS.Pipeline] Error: component "DBF Source" (1) failed validation and returned error code 0xC020801C.

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

Error: There were errors during task validation.

[Connection manager "DBF Connection"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

I have configured the 'DBF Connection' as suggested in http://www.midnightdba.com/Jen/2010/08/tip-connect-to-dbase-files-in-ssis/ and works find without config.

I have not changed anything but just created a config file and I am using it on the same machine, with the same settings. Any help is appreciated. Thanks again.

sql
sql-server
sql-server-2008
ssis
dbf
asked on Stack Overflow Jun 7, 2014 by user3577463 • edited Jun 7, 2014 by Erran Morad

1 Answer

1

Apologize for not being able to answer this earlier and thank each and everyone of you for taking time to look into this issue. I was able to finally get the package working by using the UNC path in my package. I saw that my package was working fine when run in the test environment but was failing when I created a configuration file. So I decided not to change anything in my Config file and the only way I thought I could do this was to use a UNC path (for the connection strings) which would allow me run the package with configuration file but without modifying anything in configuration file in production.

answered on Stack Overflow Jul 27, 2014 by user3577463

User contributions licensed under CC BY-SA 3.0