How Do I Update Records with SSIS and NOT INSERT new rows!?
Here is my dilema:
I need to pull information from one database (we'll say database A) and update records in another database (database B) based on common information in both databases.
I already have an SSIS package that can essentially get the correct information I need (using the Lookup Dataflow), but the problem I am having is that it seems that all I can do is "insert" new rows into database B. If I export the data to a flatfile, all is well, but that's not what I want. My goal is to use SSIS to update records in database B to have information from database A, but if it doesn't exists in database B, then we cannot add it.
Any ideas? From what I can see and read, all of the dataflow destinations do INSERTS only, but I want simply to update records. (The only reason I haven't done this with T-SQL scripting is because I felt that SSIS would be a better tool given that there are some data conversions that must take place since the comparing columns in database A and database B are of different types, but essentially the same data.)
I need to pull information from one database (we'll say database A) and update records in another database (database B) based on common information in both databases.
I already have an SSIS package that can essentially get the correct information I need (using the Lookup Dataflow), but the problem I am having is that it seems that all I can do is "insert" new rows into database B. If I export the data to a flatfile, all is well, but that's not what I want. My goal is to use SSIS to update records in database B to have information from database A, but if it doesn't exists in database B, then we cannot add it.
Any ideas? From what I can see and read, all of the dataflow destinations do INSERTS only, but I want simply to update records. (The only reason I haven't done this with T-SQL scripting is because I felt that SSIS would be a better tool given that there are some data conversions that must take place since the comparing columns in database A and database B are of different types, but essentially the same data.)
QPR
Why not use an execute sql task rather than a data flow
oops just re-read... what you are trying to do sounds like a data pump task to use a DTS term. Copy column etc. To my knowledge this is importing data into table B from table A (in other words an insert)
A sql task within your package can work fine. Google "update based on a join"
A sql task within your package can work fine. Google "update based on a join"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.