XML Network Call to Application from SQL Server 2005

Question

We don't really have a DBA here, just a semi-experienced SQL Developer and me. I've only got about a year's experience myself and we're the only programmers for our small business.

We're trying to integrate a ticketing system in our billing software with a set of stored procedures and jobs that will help us automatically determine daily whether or not we need to change the status of an account so our Tech Support department knows to look into it.

The other developer asked me to look into SSIS as a way of exporting an XML file with calls to the billing software, but I'm not convinced we're on the right track. The API for the billing software does support XML calls, but we'd need to be able to make a network call from SQL Server to the proper port for the API. Both are on the same server and have tables which exist within the same database. We're trying to go through the API because we're concerned about allowing the primary database to make changes to the extremely convoluted billing database, as it could cause unforeseen problems and would not update the billing software's internal tracking system properly.

Is there a way to make the XML API network call from SQL Server? If not, is there a workaround that might allow us to go through the API like this?

The billing software is called Platypus by TuCows, though I doubt anyone's familiar with it and this server is running SQL Server 2005 for the moment, though we'll have 2012 implemented in another month or so.

Thanks so much for your time!

Answer 1

So one potential way of doing this would be to write your SQL Server query to output an XML string of the data you want to send by using FOR XML.

You could then create an SSIS package and use a Data Flow Task to save the XML to an XML file, meaning you're logging all the exports made.

A script task, following the success of the Data Flow Task, can then do the API call. You'd write standard C#, grabbing the file and calling the API method. You would then use the Task result variables to return Success/Failure based upon the response from the API.

However, you've said you're more of a C# Dev, so why not just write a stand alone App that does all this? It would be much more robust than SSIS!

Update

There is actually a Webservice task in SSIS that can apparently do what you'd require. However, I believe that if you're more comforatble developing in C#, that is the approach you should take.