April 10, 2008 at 2:49 pm
Ok, hope I can explain this well:
I have an SSIS package currently with two data sources; one is a flat file data source(delimited text file) and the other is an OLE DB souce (SQL server table). Using the merge join task I am joining on an ID field in both sources to get the matches between the two to form a new filtered subset. (I'll call it Subset A). I now need to take Subset A and do a few things:
I need to delete records in a SQL table based on the key in subset A
I need to update records in another SQL table based on the key in subset A
I need to insert Subset A in yet another SQL table (this I know I can do by just adding a Data Flow Detination)
I'm trying to figure out which tasks I need to use to accomplish the first two bullets. I took a look at the OLE DB Command Transformation but this seems suited for running SQL commands on each row in a dataset. I need to take my Subset A and either update or delete from a table if it exists in Subset A. Anyone know the best way to do this?
Thanks,
Strick
April 11, 2008 at 9:33 am
The OLEDB Command is what you would want to use in SSIS.
Yes, it will insert or update one row at a time. That is what SSIS does and it is pretty good at it, so do not be too afraid of it. If you wanted to handle this in a set-based operation, you would have to load your data set into a staging table and in your control flow use a SQL Command to execute the insert or update from your staging table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply