none
Update in OLEDB Command SSIS is not working

    Question

  • Hi All-

    I have a problem,

    I have an update statement like below in oledb command

    ORACLE TABLE

    UPDATE Table1 SET Column1 = Coulmn1 Where ID = ID (Where Coulmn1  I have added in external column)

    Since both side are equal i,e Column1 = Coulmn1 it is not updating.

    UPDATE Table1 SET Column1 = "Test" Where ID = ID --> This is working

    UPDATE Table1 SET Column1 = Coulmn1Value Where ID = ID --> This is giving invalid identifier error

    Please help me

    Thursday, July 25, 2013 3:28 AM

Answers

  • Just a sidenote:

    the OLE DB command is the most horrible way to do updates, as it is painfully slow on large datasets.
    In most cases  you're better off dumping the data to a staging table and by writing a decent UPDATE statement.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 25, 2013 8:32 AM

All replies

  • Please help me on this ASAP.
    Thursday, July 25, 2013 7:43 AM
  • What is Coulmn1Value?

    Kind regards M

    Thursday, July 25, 2013 7:50 AM
  • Don't you need to set question marks instead of Coulmn1 ?

    UPDATE Table1 SET Column1 = ? Where ID = ID

    ps: remember this is a forum with unpaid volunteers. Shouting ASAP is not really the polite way to go.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 25, 2013 7:51 AM
  • Don't you need to set question marks instead of Coulmn1 ?

    UPDATE Table1 SET Column1 = ? Where ID = ID

    ps: remember this is a forum with unpaid volunteers. Shouting ASAP is not really the polite way to go.

    Your suggestion will work with an OLE DB Provider for SQL Server but suspect that would not work with an OLE DB provider for Oracle.

    Besides the original SQL Statement is nonsensical (excuse the word, no offence meant), I would have expected something like:

    UPDATE Table1 SET Column1 = ? Where ID = ?

    and not

    UPDATE Table1 SET Column1 = ? Where ID = ID

    'Where ID = ID' is probably going to update every single row in the destination table with the supplied parameter for every single row that passes through the data flow. This is because 'Where ID = ID is by definition true for each row in the destination table.


    Kind regards M

    Thursday, July 25, 2013 8:03 AM
  • Thanks!!

    I got some improvement now.

    UPDATE Table1 SET Column1 = "Hai" Where ID = ?

    The above one is working for me.

    Now I need to know on how I'am going to assign dynamic value to Column1.

    Please help me

    Thursday, July 25, 2013 8:20 AM
  • UPDATE Table1 SET Column1 = ? Where ID = ?

    Kind regards M

    Thursday, July 25, 2013 8:28 AM
  • Besides the original SQL Statement is nonsensical (excuse the word, no offence meant), I would have expected something like:

    ...
    I stopped trying to make sense of the forum questions a long long time ago ;)

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 25, 2013 8:31 AM
  • Just a sidenote:

    the OLE DB command is the most horrible way to do updates, as it is painfully slow on large datasets.
    In most cases  you're better off dumping the data to a staging table and by writing a decent UPDATE statement.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 25, 2013 8:32 AM

  • I stopped trying to make sense of the forum questions a long long time ago ;)

    Wise man, I guess that is why I've kept quite for many years :)

    Fully, agree about your comment re performance of updates using an OLE DB Command. Typically, I would only use them in an SCD scenario (and if the dimension table is not large)


    Kind regards M

    Thursday, July 25, 2013 8:37 AM