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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You still need to have a trigger but the stored procedure part can be replaced by SSIS package(s). You can execute the SSIS package from within a stored procedure though. You can also try to fire trigger when a SSIS package is executed from GUI/command line and in turn execute a stored procedure which executes an SSIS package. SSIS has some built in triggers/events which fire some code of its own which cater to database migration and ETL from OLTP to OLAP.

If you need to initiate an SSIS package based upon additions/changes that would be made to a db table, then use xp_cmdshell to call dtexec.exe. Or, set up a SQLAgent job and call it using sp_start_job.

Visit this link:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Based on your scenario I don't think SSIS is the correct solution. SSIS is great for scheduled tasks, repeatable processes involving multiple data sources. For instance, if you need to connect to App A and select the data to bring in, then SSIS would be good. But if you are working within your own database - stick to the Stored Procedure.
If the transaction needs to be real-time, and I sense it does because you are using a trigger - then I would stay with that approach. SSIS packages could be called via a trigger but a Stored Procedure is the more appropriate in my opinion.
Good luck!
kevinvw1Author Commented:
Thanks for the input.  That is pretty much what I was concluding also.

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?  
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.
Has the vendor given you any more info about what web services they support? i.e. can you call a web service exposed by App A to get a list of contacts?
I would use this as an opportunity to do some work with web services rather than SSIS or T-SQL
The way it would roughly work is that you would have a scheduled task which queries the web service provided by App A and gets the list of contacts which you then merge with your list of contacts in App B.
Its not real time but its close, depending on your schedule.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.