question

PauMaym-1331 avatar image
0 Votes"
PauMaym-1331 asked MartinJaffer-MSFT commented

DataFactory - drop table if schema changes

Hello,

Refering to Auto-Create table on Copy activity.

When the table is already on the database and there is a missmatch on the table schema the pipe fails. This is normal function.

I should delete the actual table on my DB and then the table will be automatically created.

My question is... is there any option to make that happen? So when the schemas are not the same the table in DB is automaticalli droped and then recreated on the new schema.

Regards,

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @PauMaym-1331 and welcome to Microsoft Q&A.

I think we can make a solution. The solution will have 2 copy activities. The first is the version you have that fails when the schema changes. The second is the one that always drops the table. We use an on-failure dependency to connect them. This way, if the schema is the same, the first copy activity suceeds and all are happy.
When the schema is not the same, the first copy fails, this causes the second copy activity to run, which drops and re-creates the table before copying data.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yeah! You're right! I didn't know on the option on failure for a copy activity! Thats Awesome!

Thank you Martin!

0 Votes 0 ·

Thank you for the feedback. Please mark as accepted answer if it solved your issue.

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered PauMaym-1331 commented

There is a pre copy script option that can run a query before the data load start. You can make use of if below way but this way it will simply drop the table every time

118957-image.png




Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav


image.png (26.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello VaibhavChaudhari,

If i do so it will always drop the table, i just want to drop the table if the schemas are not the same. I already have a Pre-Copy Script that is deleting some data from the table before the insert.

Thanks,

0 Votes 0 ·