Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

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.
0
kevinvw1
Asked:
kevinvw1
3 Solutions
 
k_murli_krishnaCommented:
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:
http://database.ittoolbox.com/groups/technical-functional/sql-l/how-to-update-a-table-as-a-record-is-inserted-using-a-ssis-package-1530101
0
 
HoggZillaCommented:
Hi,
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!
0
 
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?  
0
 
HoggZillaCommented:
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.
0
 
nmcdermaidCommented:
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.
 
 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now