0

I am using an OLE DB Command to update records in a table. I want to seperate rows that update successfully from rows that do not update (different than error). Some rows will not update because the key I am updating does not exist. This is different than an error, because the command ran so I can not use the red error line. The only idea I have would be the equivalent to when I execute the update in SQL Server and it says "(0 row(s) affected)" and I would be able to do a comparison.

Since this does not count as an error in SSIS, I can't use the red error line. Does anyone know how to catch records that do not update?

wildplasser
  • 43,142
  • 8
  • 66
  • 109
user1167865
  • 115
  • 6

1 Answers1

0

catch it in a table

Select *
INTO Some_table
FROM Table_you_are_updating_From as a
WHEre NOT EXISTS(Select *
FROM Table_you_are_updating as b WHERE a.key=b.key )
Ruzbeh Irani
  • 2,318
  • 18
  • 10
  • Thanks Ruzbeh, that would work for the question I asked, but I don't think it will work for the problem I am running into. My scenario is that I need to transfer data from the source to the destination. After updating to the destination, I need to go back and mark source records that completed successfully. So in theory, I will need a conditional split or some other method to seperate successful from nonsuccessful. Another way would be to have an on successful action. Do you know how this could be achieved? Thanks for the help! – user1167865 Aug 01 '12 at 21:27
  • http://stackoverflow.com/questions/4340621/ssis-update-source-records-after-transfer may be you could use this answer for marking completed on source and destination server. – Ruzbeh Irani Aug 01 '12 at 21:31
  • if you find the answer right please accept the solution.. thanks – Ruzbeh Irani Aug 01 '12 at 21:40
  • Is there a way to define an On Update Success action? You are right where it will be better to do the action on success. – user1167865 Aug 01 '12 at 21:42
  • you could have a updation_date column and and while updating put in the timestamp then you could check if the updation date is within certain timeframe then these rows are updated from the previous process....similarly a column can be added call updation_id and can be updated with the max+1 of updation_id and then u can check which group contains the max updation_id – Ruzbeh Irani Aug 01 '12 at 21:46
  • Unfortunately we are not allowed to add any new columns or tables to the destination system. – user1167865 Aug 01 '12 at 21:49
  • you could use output tables then http://msdn.microsoft.com/en-us/library/ms177564.aspx – Ruzbeh Irani Aug 01 '12 at 21:52
  • I ended up creating a link server in SQL Server and stored procedures. From SSIS, I then call the stored procedure and handle everything in the SQL Server stored procedure. This way I take most of SSIS out of the picture and only use it when I need to execute the stored procedure. – user1167865 Aug 02 '12 at 20:13