Should I use SSIS for this?

Posted on 2008-10-27
Last Modified: 2013-11-10
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.
Question by:kevinvw1
LVL 17

Accepted Solution

k_murli_krishna earned 200 total points
ID: 22819553
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:
LVL 17

Assisted Solution

HoggZilla earned 200 total points
ID: 22820477
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!

Author Comment

ID: 22824120
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?  
LVL 17

Expert Comment

ID: 22824177
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.
LVL 30

Assisted Solution

nmcdermaid earned 100 total points
ID: 22828737
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.

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now