ADF fixed length of string variables

Question

Hi, I have a dataflow with a sink as an sql table that is created dynamically. When I execute the pipeline, the string variables are stored in SQL Server with type nvarchar and I need the types to be static like varchar. Can I do this directly in the ADF dataflow without having to modify types in SQL. Thanks in advance.

Answer 1

Hello @FatiMa06 and welcome to Microsoft Q&A.

If I understand correctly, DataFlow sink is creating new table in SQL, and you want precise control over the data types. Data Flow sink takes type information from the sink.

I'm not sure what you mean by varchar being static and nvarchar not being static.

I can see two ways to fix the schema.

First, if you are using a dataset rather than inline, defining the schema in the daataset should inform the Data Flow. However you meantioned the table is dynamic, so I suspect this mean you do not know the columns beforehand.

So the other way, is to use a pre-SQL script (found in sink settings) to make the table and set its schema. I think we can make this script dynamically determine the schema.

The keys to this script are the functions:

• ColumnNames: this gets the names of the columns in a stream. drifted optional. returns array
• type: this tells you the in-DataFlow data type of a column
• map: this iterates over each element of an array and applies function to it.
• reduce: applies an expression to collect an array into a single value
• array: makes an array from input

I think it would work something like:

array(concat(
    'Create Table mytable (',
    reduce(
        map(
            columnNames('mystream'), #item + ' ' +
            case(
                type(#item) == 'String', 'varchar(100)',
                type(#item) == 'Integer', 'int'
                ...
            )),
        '',
        #acc + ', ' + #item,
        trim(#result, ',')
    )
    ,');'
))