SQL Server – How to Backup to a Network Share Through a Separate NIC

In my opinion, log shipping has those advantages:

1. I understand the direction is only one-way (master to slave). Replication is a complex solution better suited to two-way data replication or replication to multiple sources.

2. Log shipping doesn't need a special design of the database (e.g. primary keys as GUID's and not as autoincrement integers).

3. Log shipping is a less drain on resources, is easier to set up and maintain.

As the question is quite broad and opinion based, I can't give you an ultimate solution. This is just a bit of advice that you may hopefully find helpful.

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.