Data Factory Copy function is not copying table correctly. Columns order is not correct.

Question

I have a simple pipeline with 1 single copy function. It copies the calendar table from salesforce to a sql server database. This works perfect in our dev data factory. We are using dev salesforce and dev sql server.

We moved this to our test data factory and it no longer completes. We are using test salesforce and test sql server. It tries inserting the data in the wrong column order. It tries putting "user" in the boolean data type field and copy fails.

I thought maybe it was salesforce test or sql server test causing the issue, so I created a clone of the pipline and changed the source and sink to use salesforce test and sql server test but from my DEV data factory and all worked as expected.

I then changed data factory TEST to hit salesforce DEV and sql server DEV and this fails as well with the same insert error.

So I believe I can say for sure it's a data factory issue in my TEST environment.

My SHIR is the exact same version in DEV and TEST.

What else could be different?

Answer 1

Hello @Blasko, Dan W

When you edit the Copy Task you will see a Mappings menu

Fro there you Map which columns goes to which column and you can also update he schema

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping

Find the selection and make the correct mappings ! It will work !

--

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

Hello @Blasko, Dan W

If you Import Schemas , the Mapping will be done automatically

You need to have the Tables ready on the Destination though

--

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 3

FYI if anyone stumbles across this topic.

This pipeline works if I use the legacy sql server connector.

If I use the recommended sql server connector I get the inserts out of order on random tables. I get columns do not exist on random tables. It is 100% related to the new sql server connector.