CODEWITHSUNDEEP
sql-serverxmlazureazure-sql-databaseazure-webjobs
TheDoc
TheDoc

Reputation: 718

Azure service to receive XML data and import into SQL database

I need to set up a service on my Azure account that will receive XML data and import it into an Azure SQL database. I want to be able to connect to Azure from a remote server (where the XML data will be created) and upload the data where it will be automatically parsed and inserted into the SQL db. The XML data will all be formatted with the same column setup and data types, so the parsing shouldn't be difficult. Where I'm stuck is just trying to figure out how to set up a simple service that receives and parses the data. I don't want to do this through an import/export wizard, a website frontend, or anything like that. It needs to be automated so that when it receives the data it goes to work.

Please note, I'm not really asking for code to solve my problem. I'm fine with learning how to do it myself, but I can't seem to get started without having to learn the entirety of no less than 3 new languages... Any help on searches to help, or where to get started?

EDIT: This is the closest thing I can find so far. http://www.freshconsulting.com/building-data-import-tool-azure-webjobs/ The only issue with it is that I may not have access to an SMTP relay to send files, so I would need to be able to just send POST data (or REST data...?) and have the data either reconstituted into an XML or CSV file when it gets to Azure, or have it read in dynamically in real time. The first option seems the most plausible. Anyone have suggestions?

Upvotes: 2

Views: 3897

Answers (2)

JoshL
JoshL

Reputation: 1716

You've got a few options.

You can write all the code yourself. There's nothing stopping you from standing up a simple HTTP front-end and POSTing your XML there. This would be (say) a simple Web API hosted as an Azure Web App, that accepts your XML and shreds it into your SQL database using Entity Framework, etc.

https://azure.microsoft.com/en-us/services/app-service/web/

This has the advantage of being simple, but with the potential downside of inefficiency if your XML is very large or if you upload frequently (have to be careful about buffering, etc.).

Like stephgou mentions you can use a WebJob to listen on a queue or blob container and trigger shredding and SQL inserts upon new items showing up in the queue/blob container. You'll still need a way to get the data there in the first place. That could be an HTTP API you write or perhaps you could do manual uploads using a third party tool:

http://storagetools.azurewebsites.net/

...or Powershell:

https://azure.microsoft.com/en-us/documentation/articles/storage-powershell-guide-full/

There's also a feature called Azure Data Factory which in some sense is "SQL Server Integration Services in the cloud"... you author ETL pipelines to bring data into Azure, shred and transform it as needed, then process it (in your case, "processing" is just writing it to SQL):

https://azure.microsoft.com/en-us/services/data-factory/

One other thing to consider is cost. Standing up a dedicated HTTP front-end to do infrequent uploads might not be the most effective use of your money. Services like Data Factory have the advantage of being billed per use (more or less, see details here... https://azure.microsoft.com/en-us/pricing/details/data-factory/). Your mileage can and will vary, the point is that you need to think about the resources you'll consume, for how long, and how often, in order to factor in cost over time.

Best of luck!

Upvotes: 1

stephgou
stephgou

Reputation: 199

One way to implement this kind of solution would be to use webjob and trigger the xml update to sql on each xml new message on the azure storage (Queue or Blob).

Using the WebJob SDK you may use the QueueTrigger attribute

public static void HandleXMLdataFromQueueStorage([QueueTrigger("logqueue")] string logMessage, TextWriter logger)
{
    logger.WriteLine(logMessage);
}

https://azure.microsoft.com/en-gb/documentation/articles/websites-dotnet-webjobs-sdk-storage-queues-how-to/

This is the recommended method (speed, reliability).

You may also use the WebJob SDK with the BlobTrigger attribute

public static void HandleXMLdataFromBlobStorage([BlobTrigger("input/{name}")] TextReader input)
{
// update SQL
}

https://azure.microsoft.com/en-gb/documentation/articles/websites-dotnet-webjobs-sdk-storage-blobs-how-to/

The WebJobs SDK scans log files to watch for new or changed blobs. This process is not real-time; a function might not get triggered until several minutes or longer after the blob is created. In addition, storage logs are created on a "best efforts" basis; there is no guarantee that all events will be captured

Hope this helps Best regards Stéphane

Upvotes: 1

Related Questions