SSIS 2008: Hi, I am trying to build a DataFlow step in order to update SQL Server Table row-by-row. Source is Flat file (delimited) with 100 fields that would map to 100 columns in SQL Server table. Target is SQL Server table. What are my options to build this DataFlow step? Is there a best way to update SQL Server Table row-by-row (other than stage table). Key notes: This app will be getting thousands of files every day. Each file will have only handful of update records (may be, maximum a hundred records in each file). Stage table approach is not an option at this moment - don't ask me why?!! It's long story. (and I know how to do it using Stage table). It's okay to process row-by-row at this moment (in fact, row-by-row is the best option at this moment based on our architecture of DatawareHouse). So, in short, what are my options to update SQL Server Table row-by-row (other than stage table). Few Links would help. Thanks in advance.

use a Data flow task with flat file source and then use OLEDB Command , write your update command there,your update command can be look like this: update mytable set field1=? , field2=? where id=? in this statement , question marks are parameter marker, then in columns tab of oledb command you can map input columns with these parameters. http://www.rad.pasfu.com

Thanks, Reza. That would surely work. Do you know if I use "OLE DB Destination" with SQL Command (instead of "OLE Command"), will it work?

Thanks, Reza. That would surely work. Do you know if I use "OLE DB Destination" with SQL Command (instead of "OLE Command"), will it work? oledb destination is for INSERT not update, and will not accept input parameters.http://www.rad.pasfu.com