As you are familiar with SSIS, you will probably know that you can redirect errors to an error table.

If your target staging table is set up with all of the...

What action do you wish to take if validation fails (eg, invalid date) for a particular row?

To fix the issue with not being able to edit scripts - you need to repair 'Microsoft Visual Studio Tools for Applications YYYY'.  This happens almost every time you...

Not sure about stopping it, but looks easy enough to handle in the proc, based on the differing lengths of the incoming string.

Aha, you're using Polybase. I thought you were using 'external' to mean 'somewhere not on this database/instance'!

I have not used it and therefore can't help, sorry.

I have no link for VS Pro, you need to buy it or create a VS subscription.

Before you do that, can you try creating a brand new package and adding...

Sorry, I am new for cloud technology. so external here means we only store metadata of tables in our Azure SQL database that exist in other Azure SQL database....

One more issue arose. I cannot edit my C# Scripts... I hit the button and C# won't launch... I ran the repair on VS 17 and restarted my machine...

Possibly something to do with ProtectionLevel,...

I would add an ORDER BY Id clause to your SELECT, to help ensure consistency between the two versions of the query.

Out of interest, could you explain the background to this requirement?

Have you thought about tracking down all missing schema references in the master (I assume 'dbo') schema and adding them?...

I would have expected it to report a failure in that case, but to be brutal you could add a Script Task to your OnError handler which does this: