Should I use SSIS for this?

Posted on 2008-10-27
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 17

Accepted Solution

k_murli_krishna earned 800 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 800 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 400 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 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