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.)