I have a SQL table that is going to be acting as a staging table for data coming from one application (App A) and going into another application (App B).
The table will be populated with new and changed company/contact records from App A.
I have no control over App A. It is essentiallly a black box. All I can see are the new/changed records being pushed from App A into the staging table.
I have to take these records and compare them to the App B data using a simple Account Number match and/or possibly an email match. I then either update a matching record in App B or insert a new record into App B.
In the past I have put an INSERT trigger on the staging table and called a stored procedure when a record gets inserted. My SP would then perform some matching logic and then either update or insert records accordingly.
Also, one other requirement is that I must use Stored Procedures (or Web Services) to insert/update data into App B.
I am trying to determine if this is a good time to get my feet wet with SSIS or should I just go with the method that I already know?