How to get the schema of a on-premises sql table using Azure Custom Activity along with the data?

Question

I have created a .Net Activity which has copy activity (which copies the data from on-premises to Data Lake store) and U-SQL activity (which process the data and store the result in Data Lake Store).

But for processing the data using U-SQL needs the schema of the table which is also need to copied from the on-premises database.

U-SQL Query:

      DECLARE @storagePath string = @"E:\";
      DECLARE @inputFileSetName string = @"inputfile.tsv"; 
      DECLARE @outputFileName string = @"outputfile.tsv";
      DECLARE @input string = String.Concat(@storagePath, 
      @inputFileSetName);
      DECLARE @output string= string.Concat(@storagePath, @outputFileName);

      @searchlog =
       EXTRACT <Schema for the table>
       FROM @input
       USING Extractors.Tsv();

      OUTPUT @searchlog 
       TO @output
       USING Outputters.Tsv();

I have to get the schema of the table along with the data using the Azure ADF Custom Activity?

U-SQL supports schema-on-read but the schema must be supplied at design time not run-time, ie U-SQL does not support dynamic U-SQL at this time. In your other question I have given you examples of creating USQL dynamically (which is a slightly different thing). Maybe if you can take a step back and describe what you are trying to do without mentioning technology (like ADF, .net activity, U-SQL etc) someone will be able to help you. — wBob

Michael Rys Michael Rys · Accepted Answer · 2017-08-18T00:40:55

How may different schemas are you expecting? As Bob mentions in his comment, we do not support dynamic schema discovery in U-SQL at the moment.

Also, what are you planning on doing with the schema rowset later in the script if you don't know what to do with it.

If you just want to copy a file without understanding the schema (as in your example above), I would suggest to use the file system APIs or ADLCopy (which runs a variant of this U-SQL code).

How to get the schema of a on-premises sql table using Azure Custom Activity along with the data?

1 Answers