Azure Data Sync - Copy Each SQL Row to Blob

Question

I'm trying to understand the best way to migrate a large set of data - ~ 6M text rows from (an Azure Hosted) SQL Server to Blob storage.

For the most part, these records are archived records, and are rarely accessed - blob storage made sense as a place to hold these.

I have had a look at Azure Data Factory and it seems to be the right option, but I am unsure of it fulfilling requirements.

Simply the scenario is, for each row in the table, I want to create a blob, with the contents of 1 column from this row.

I see the tutorial (i.e. https://docs.microsoft.com/en-us/azure/data-factory/data-factory-copy-activity-tutorial-using-azure-portal) is good at explaining migration of bulk-to-bulk data pipeline, but I would like to migrate from a bulk-to-many dataset.

Hope that makes sense and someone can help?

Why would you migrate text to BLOB when you have Table Storage? — paparazzo
In this case it's massive quantity of text for each row, and for ongoing use, Blob was the right store for us — Nagoh

wBob wBob · Accepted Answer · 2016-11-16T21:29:56

As of now, Azure Data Factory does not have anything built in like a For Each loop in SSIS. You could use a custom .net activity to do this but it would require a lot of custom code.

I would ask, if you were transferring this to another database, would you create 6 million tables all with the same structure? What is to be gained by having the separate items?

Another alternative might be converting it to JSON which would be easy using Data Factory. Here is an example I did recently moving data into DocumentDB.

Copy From OnPrem SQL server to DocumentDB using custom activity in ADF Pipeline

SSIS 2016 with the Azure Feature Pack, giving Azure Tasks such as Azure Blob Upload Task and Azure Blob Destination. You might be better off using this, maybe an OLEDB command or the For Each loop with an Azure Blob destination could be another option.

Good luck!

Azure Data Sync - Copy Each SQL Row to Blob

2 Answers