How to reference Input Columns in SQL Command for OLE DB Destination?

Question

I have Data flow task. Final step of it is the OLE DB Destination component. My goal is to update existing table. The table has fields Id, Query and some others. The OLE DB Destination has input columns rowId, queryString. I'm trying to use DataAccessMode: SQL command. But if I write something like

UPDATE [queryTable]
SET [Query] = [queryString]
WHERE [Id] = [rowId]

I get error "Invalid column name 'rowId'"

I guess I should write something like [Input Columns].[rowId] but I don't know exactly.

Answer 1

Well, you want to update an existing table in SSIS. You can approach it in two ways.

1. Create an OLE DB Command Transformation as the final step. Simple, for your sample you should write something like

   UPDATE [queryTable]
   SET [Query] = ?
   WHERE [Id] = ?  
   

   and on the Column Mapping tab of editor - map the first parameter to queryString field and the second - to rowId.
   Simple, easy to do, but not performant - each line will be processed in its own SQL transaction.

2. Create a buffer table in SQL, clear it (just in case), store all changed rows there with OLE DB Destination. Then on the step next to Data Flow, which will be Execute SQL Task, update your table with data from your buffer table with some SQL command like

   UPDATE [queryTable]
   SET [queryTable].[Query] = [buffer].[queryString]
   FROM [queryTable] 
   INNER JOIN [buffer] ON [queryTable].[Id] = [buffer].[Id]  
   

   Well, it will be way faster, since all modifications are will be in one transaction. But - the SSIS package will be more complicated and you have to create an additional table.