azure data factory - How To Trim Whitespace for CopyData

Question

I'm using the CopyData component to Extract-Load data from pipe-delimited files in to Azure SQL DW.

Generally this is working fine, but it seems the default behavior is to not trim whitespace on string columns in the delimited file. So the sink to the Azure SQL DW table can't handle the column varchar width.

Example

"Value Name Example                                      "

This won't work with a VARCHAR(20) table column target. Truncation error occurs.

What is the normal practice for this? Is it considered "Transform", and so we have to resort to SSIS or other compute?

Thank you.

DTRT · Accepted Answer · 2018-08-17 20:42:48Z

1

The lack of a JSON trim() expression notwithstanding, expressions don't seem to work in the dataset mapping table...(hopefully) yet.

If you have a SQL Server instance available, you can pass the data set as a table value to a Stored Procedure that trims the fields and performs any other necessary modifications.

answered Aug 17, 2018 at 20:42

DTRT

11k15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

azure data factory - How To Trim Whitespace for CopyData

Example

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Example

1 Answer 1

Comments

Related