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!