Export Data to MS Access file

Hi guys,
I use Data Flow Task to export data from SQL table to MS Access file.
In Connection Manager for Data Flow Destination task I specify
provider "Native OLEDB/MicrosoftJet 4.0" and path to Access file
in Database file name box. In design time I have no validation error.
Everything looks fine.
However in run time mode I'm getting error:

"Error: 0xC020801C <Datataset name> , MS Access File [1459]: The AcquireConnection method call to the connection manager
<connection manager name> failed with error code 0xC0202009.
Error: 0xC0047017 at <Datataset name>, DTS.Pipeline: component "MS Access File" (1459) failed validation and returned error code 0xC020801C."

Any thoughts?
Thanks.Anyone? :)|||Are you able to use your Access data source outside of SSIS?

Ideally, could you create a small .NET application to open an OLEDBConnection with the same connection string the SSIS connection manager has.

Can you also post the connection string from your Access connection here?

I saw no problems with using Access connections in SSIS designer (and Import/Export wizard) on my machine.|||Ok, what I figured out is if Access application (or Jet engine) isn't installed,
you cannot export data to mdb file.|||A Windows computer without the Jet engine should be a rare occurrence...beginning, I believe, with XP, it is installed with the operating system, independently of the Access application that uses it as its default database format.|||Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.|||I'm having the same problem. But I'm trying to import data from an access file. Can you let me know how you resolved your problem? Did you install the Jet engine? If yes, where do you get it from? Thanks in advance for your response.|||

If I am not wrong, the only thing you need is the MDAC which you can download here http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Create an OLE DB Connection in SSIS, then create a data flow control. In the data control flow, create an OLE DB source, and then maybe a flat file or OLE DB destination. Link Them together, and test it.

Hope it goes well.

|||

Metal_Fly wrote:

Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.

Metal_Fly,

If I understand correctly; you are getting the error only at runtime when the package is executed in a 64-bit machine; right?

If that is the case; you have to run the package in 32-bit mode in the server. I am not sure how you are running the package; but it seems like is running as 64-bit; so SSIS would try to load a Jet 64-bit driver which is not registered in the system.

For running the package in 32-bit; you need to use the DTexec.exe in the Program Files (x86) folder.

The command line should look like:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /FILE "D:\Shared\DigitalCockpit\ETL\ETLControl.dtsx" /MAXCONCURRENT " -1" /CHECKPOINTING OFF /REPORTING EW

I believe you won't be able to use BIDS debugger in the server because it runs the package as 64-bit.

I am not sure if Jet is available for 64-bit; so I will leave that open to discussion.

BTW, Joseph suggestion may not work since that download is available for x86 architecture.

Rafael Salas