Using SSIS to export to MS Access

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  November 14, 2012 at 2:02 am

  #151119

  Hello people,

  I am using SSIS to export data from SQL Server 2005 to MS Access 07.

  I have an MS Access template (with predefined table and columns) which is placed on the server. I am trying export data from SQL Server to MS Access.

  MS Access is not installed on the server. Yet, when I try to map the SQL columns onto the MS Access, it shows the columns from the MS Access db (mdb file) even without MS Access being installed. Also, the connection manager returns Success when I test the connection to the MS Access. This got me confused whether this could work without even installing MS Access.

  When I try to run it, I get the following error:

  "Error: 0xC0202009 at Package, Connection manager "D:\Test_MS_Access\DEV_Access_Test.mdb": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.

  An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

  Error: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "D:\Test_MS_Access\DEV_Access_Test.mdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

  Task failed: Execute SQL Task

  Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

  SSIS package "Package.dtsx" finished: Failure."

  Is this because MS Access is not installed on the server or is it something else?

  Thanks in Advance,

  Prady