ssis want to update a sql table based on a flat file

Rather new to SSIS so not sure how to handle t开发者_开发问答his.

I have a flat file which i managed to successfully read from. So right now my data flow consists of just a flat file source.

What i want to do is something like this:

Update SqlTable S set s.columnA = f.columna from FlatFile f where s.columnID = f.columnID

Right now the only way i can see of doing this would be to insert the contents of the flat file into a sql table, then doing my update. This seems wasteful considering i don't need to save the data of the flat file. I just need to update an existing sql table based on the data in the flat file. So is there some way to run the query directly in the SSIS package instead of having to insert a bunch of data into a sql table that i will just wind up dropping?

thanks

Update SqlTable S set s.columnA = f.columna from FlatFile f where s.columnID = f.columnID

That statement above is a SQL statement. You cannot connect a sql table to a flat file. You need to work in SQL to do an update, since that is where the table lives

You have 2 choices:

1. Use an OLEDB Command component within the data flow. The downside is this calls the statement for each record, so if you have 1,000s of records it is very inefficient.
2. Push the records to a table using an OLE DB Destination and then you can call your update using an Execute SQL Task. You can then truncate the table if you like

A possible 3rd option is to roll your own OLE DB destination to do an update on record sets vs records.

While this might sound wasteful, to create a table in the database to store update records, it is done very often. You just drop the worktable or truncate when complete.

You could add an OLE DB Command component to the Data Flow that retrieves data from the flat file. The OLE DB Command would do a single row update for each record retrieved from the flat file. This might be okay if there are few rows in the flat file; but, you can imagine how bad performance will be if there are many rows in the flat file.

I think you'll find that sending the flat file rows to a database table and running a single UPDATE is going to be the best performer for lots of data.

I haven't tried this but have you tried sending to a recordset destination and then running the update using that?

The bulk load into a temporary table is the way to go and then do your updates from the temp table. As a previous poster says it is quite a common aproach to stuff data into a staging area prior to doing some more work with the data and then dropping or truncating the table