I need to import CSV rows into one column in a staging Azure SQL table, ignoring the column separators

Question

Requirement: to import csv rows into one column of a staging table, then slic up the data using a stored procedure
Currently: Source data is CSV & being uploaded to our Azure Blob Storage. The arrival of a file triggers the pipeline. & the columns are mapped to a table type in Azure SQL & moved to a table using a stored procedure etc.
New Requirement: The incoming CSV files are going to start getting new columns added without us knowing, so we have written a Stored Procedure that reads a CSV ROW and analyses it for newly added columns. That logic looks OK
Problem: How do I build a Copy Data pipeline that reads a CSV file row by row, ignoring columns? Should I be using a Blob file as the Source type? I have read tutorials, watched videos .. but am still struggling to find how to do this

Thanks

Answer 1

Hello @Darren McGlenchy and welcome to Microsoft Q&A. Please allow me to lay out some options.

For reading a CSV and deliberately ignoring column delimiters, read using a Delimited Text dataset on Blob linked service. In the dataset configuration, set the column delimiter to "No delimiter" or to a character which does not exist in your data. For such a character, I like to use control character such as Bell.

For actually moving the data there are a couple options.

In the Copy Data activity, when using Azure SQL dataset, there is an option to use table or query or stored procedure. Stored procedure is one option.

Another, slower way is to use a Stored Procedure activity to push in data. This method first uses a Lookup activity to fetch records from the csv, then uses a ForEach activity to iterate over each record, and inside the ForEach uses Stored Procedure activity to push to database. This method has limitation of maximum 5000 records returned from Lookup. It is also slower, and more expensive.

Let me know which you prefer, and I will make pictures.