locked
Update a table in an Access Database Through an OLE DB Command RRS feed

  • Question

  • I have a SQL Server 2008R2 SSIS package (running on 64 bit) where I need to run an update against a MS Access database table from a SQL Server table. The version of Access is 2010.

    In the package, I have an OLE DB Command container. I have Available Input Columns from the SQL Server source, but no way to identify the Available Destination columns. In T SQL Syntax, here is what I need to do against the destination table:

     

    UPDATE Natural_Accts
    SET NatDesc = SQLServerTable.NatDesc,
    Parent1 = SQLServerTable.@Parent1,
    Parent2 = SQLServerTable.@Parent2,
    Parent3 = SQLServerTable.@Parent3,
    Parent4 = SQLServerTable.@Parent4

    WHERE NatCode = SQLServerTable.NatCode

    The provider that I am using for the MS Access Connection Manager is the Microsoft Office 12.0 Access Database Engine OLE DB Provider.

    How do I configure the OLE DB Command so that I can Update the MS Access table?

    Thank you for your help!

    cdun2

    Friday, October 14, 2011 8:27 PM

Answers

  • they way you wrote the query isn't correct.

    you should use parameter marker for OLEDB which is question mark ( ? )

    your update command should be :

    UPDATE Natural_Accts
    SET NatDesc = ?,
    Parent1 = ?,
    Parent2 = ?,
    Parent3 =?,
    Parent4 = ?

    after writing the sql statement, when you go to columns tab, you will see input columns and parameters together, just map them appropriately.

    also note that Jet has some problems with 64 bit, you can try to run package under 32 bit.


    http://www.rad.pasfu.com
    • Proposed as answer by Venkataraman R Saturday, October 15, 2011 8:43 AM
    • Marked as answer by Eileen Zhao Monday, October 24, 2011 7:04 AM
    Saturday, October 15, 2011 12:12 AM

All replies

  • they way you wrote the query isn't correct.

    you should use parameter marker for OLEDB which is question mark ( ? )

    your update command should be :

    UPDATE Natural_Accts
    SET NatDesc = ?,
    Parent1 = ?,
    Parent2 = ?,
    Parent3 =?,
    Parent4 = ?

    after writing the sql statement, when you go to columns tab, you will see input columns and parameters together, just map them appropriately.

    also note that Jet has some problems with 64 bit, you can try to run package under 32 bit.


    http://www.rad.pasfu.com
    • Proposed as answer by Venkataraman R Saturday, October 15, 2011 8:43 AM
    • Marked as answer by Eileen Zhao Monday, October 24, 2011 7:04 AM
    Saturday, October 15, 2011 12:12 AM
  • Hi cdun2,

    Other way is to use a script component.

    You pass parameters as read-only and the output result as read-write variable.

    Examples of C# code are here - http://social.msdn.microsoft.com/Forums/eu/csharpgeneral/thread/74ea5d58-ecfd-4c83-9397-70f31439fa4e

    You need to modify code to be used in SSIS.


    Sergei
    Saturday, October 15, 2011 7:56 AM