kevinvw1
asked on
Should I use SSIS for this?
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?
Thanks, Kevin.
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?
Thanks, Kevin.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I really don't suggest putting any complicated logic into the Trigger. Even a SP is a little risky. As you know, if any part of the trigger fails, even the trigger event is at risk for failing. Since the Insert event really needs to be your trigger, you are kinda stuck there. You could definately create a CLR or even execute a command line process from the trigger, really no limits - just the risk of an error and losing your Insert completely. That would SU K.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, along these lines....
I am workng with an application (App B) and the manufacture says that I can use T-SQL and Stored Procs to push data into the system but it is not "officially supported".
They officially support Web Services.
But trying to do a real time integration with a SQL trigger and a Web Service doesn't seem like a good approach.
Any suggestions on how I can get the real-time benefit of a SQL Trigger and still use the "officially supported" web services ?
Is there a way to have a trigger call a SP and use CLR to call the Web Services without having to worry about the trigger hanging?