0

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!

  • Theoretically, SSIS can do what you need, but you would be writing custom .NET (C# or VB) code inside a Script Task. Is that an option? – Mark Sinkinson Apr 9 '14 at 14:23
  • Yes, I'm actually more of a C# .Net dev with the SQL experience to write my pages rather than a SQL dev with a bit of programming exp. I was actually wondering whether or not a solution like that would work. Care to answer and be more specific? – Drew Apr 9 '14 at 14:24
0

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.

  • Mark, thank you! I'm definitely going to try and work on this. Part of the reason we were looking into how to do this was that I've been trying to learn more about SQL and, like most programmers, the other dev wants to use the tools he's comfortable with. We were hoping using SQL Server would be easier, but I agree that writing a standalone app may be the best way to go. Thanks for the input! – Drew Apr 9 '14 at 15:07
  • I wouldn't necessarily have marked this as the answer though :) You may get more detailed opinions from others that could help you further! – Mark Sinkinson Apr 9 '14 at 15:15
  • 1
    Very true, but I think it sets us on the right path and I'd prefer the more robust solution. My preference was to have an external handler instead of trying to do it all in SQL anyhow, but I wanted to do my due diligence to check into all possibilities. Plus, since it puts us on the right path, only the people who have a really excellent answer will likely spend the time on it now and you deserve the tiny points I can give you for being awesome. – Drew Apr 9 '14 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.