Order Columns in output SQL table from Data flow

Question

Is there any way to get columns to sort alphabetically in a Data flow for the Sink? Or specify some custom-sort order via a Select schema modifier operation in a Data flow prior to the Sink?

I would like this to be dynamic for whatever source table I pass into the Data flow via the parameters that have been established.

I've looked everywhere for something like this but can't seem to find anything.

Answer 1

Hello @Walter Pelowski ,

Thanks for the question and using MS Q&A platform.

My understanding is that you have a SQL source, and you would like to specify the source column order in ascending order and map to respective sink in mapping data flows. And you would like to have this as dynamic for any give source table as a parameter. Please correct me if my understanding is wrong.

I don't think we have an out of box feature in ADF to achieve this but if your source is SQL then you can use the below query or create a stored procedure out of it and use it in your Mapping data flow source to pull the table schema in an ascending order by passing the table name parameter as a variable to your stored procedure.

DECLARE @QUERY VARCHAR(2000)  
DECLARE @TABLENAME VARCHAR(50) = 'EMPLOYEE'  
  
SET @QUERY = 'SELECT '  
SELECT @QUERY = @QUERY + Column_name + ',   
'  
  FROM INFORMATION_SCHEMA.COLUMNS   
 WHERE TABLE_NAME = @TABLENAME  
 ORDER BY Column_name  
  
SET @QUERY =  LEFT(@QUERY, LEN(@QUERY) - 4) + '   
FROM '+ @TABLENAME  
  
PRINT @QUERY  
EXEC(@QUERY)  

Here is the output looks like:

Hope this helps. In case if your ask different than my understanding, please help clarify a bit with an example so that we can better assist you accordingly.

Thank you

Answer 2

Thanks. Yeah, it works. I was hoping to do the column ordering in the Data flow but instead I sink it to a DEV table and then immediately follow it up with an INSERT INTO script activity that orders the columns the way I want them. Here is what the pipeline looks like now.

Thank you again.