Solved

Should I use SSIS for this?

Posted on 2008-10-27
5
410 Views
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.
0
Comment
Question by:kevinvw1
5 Comments
 
LVL 17

Accepted Solution

by:
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:
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
 
LVL 17

Assisted Solution

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

Author Comment

by:kevinvw1
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?  
0
 
LVL 17

Expert Comment

by:HoggZilla
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.
0
 
LVL 30

Assisted Solution

by:nmcdermaid
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.
 
 
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
Live Storage Vmotion VMs with shared VMDK 10 58
Linked Server Issue with SQL2012 3 26
SQL Count issue 24 15
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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