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.

Erran Morad
  • 4,563
  • 10
  • 43
  • 72
user3577463
  • 105
  • 7
  • Can you add a link for a sample DBF file here ? I'll try to replicate your problem on my system. – Erran Morad Jun 07 '14 at 17:28
  • @BoratSagdiyev I am fairly new to www.stackoverflow.com and cannot find a link to attach the file. Is there an option to attach the sample file that I am missing? If not, I can email the file to you if you want. – user3577463 Jun 07 '14 at 18:13
  • You can post it to dropbox or something like that and then add it as a hyperlink. – Erran Morad Jun 07 '14 at 19:18
  • @BoratSagdiyev please find the link to the test file: https://onedrive.live.com/redir?resid=88A15E21428FFF39!468&authkey=!ABmTv82GUYMoeLY&ithint=file%2c.dbf – user3577463 Jun 07 '14 at 19:36
  • Seems my ssis 2008 cannot even read this file with jet 4.0 or ace 12.0 oledb drivers. It think its better to convert the dbf into sql server. I found some answers here - http://stackoverflow.com/questions/52822/how-to-import-a-dbf-file-in-sql-server See answer by `ape software`. Try to modify it per your system and run it as an execute sql task. The only problem is that you need to know the name of columns in the dbf file. To view dbf files use this - http://answers.microsoft.com/en-us/windows/forum/windows_7-files/how-do-you-open-a-dbf-file/3f05175d-3547-49ac-89a4-32e71c3c6dc4 HTH. – Erran Morad Jun 07 '14 at 20:05
  • @BoratSagdiyev Will check those links out rightaway. Thanks for your time and help. – user3577463 Jun 07 '14 at 20:24
  • I tried to get the dbf to open on my system, but failed. In the process, I found some links that might be of help. First one is a detailed tutorial on visual foxpro to sql server. When something works, please share the solution. Links are - – Erran Morad Jun 07 '14 at 20:41
  • http://www.craigbailey.net/vfp-importing-vfp-data-into-sql-server/ , http://stackoverflow.com/questions/16819811/how-to-convert-visual-foxpro-database-into-sql-server-database , http://kethare.in/2012/01/07/step-for-migrating-data-from-foxpro-database-to-sql-server-using-ssis/ – Erran Morad Jun 07 '14 at 20:42
  • Any success so far ? If you solved it, then please share a brief outline of the solution with us. Thank you. Chenqui. – Erran Morad Jun 08 '14 at 23:11
  • Last time I did a FoxPro conversion, I had to use an older version of access as a middle step in the conversion due to this issue. VFP->Access 2003->SQL Server. – Eric Hauenstein Jun 09 '14 at 12:46
  • What attributes are you writing to your config file? – TMNT2014 Jun 09 '14 at 14:38
  • The first error message says "There may be error messages posted before this with more information on why the AcquireConnection method call failed". If there were earlier messages, could you add them to your question? – Ann L. Jun 15 '14 at 19:25

1 Answers1

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.

user3577463
  • 105
  • 7