Have you got both the 32- and 64- bit versions of the drivers installed?

(SSDT uses the 32-bit version and (by default) DTExec will use the 64-bit version – might as...

DACPAC will do that. If you are not familiar, there is plenty of material available online. The process should be straightforward:

1. 'Extract' a DACPAC from the existing database.
2. Create a...

It is likely that you will get more responses if you post your sample data in consumable format (ie, with DDL and INSERT statements).

I have used that before.. but was looking for something that would dump tables in order with foreign key reference in mind, so I could pickup this file and...

As the column names are not static, doing this dynamically is probably the only way you will get this to work.

The answer is 'almost certainly'.

Now, if you will take the...

I hate it when something fails, but only some of the time. Makes troubleshooting all the more challenging.

Can you see any sort of pattern between executions which succeed and those...

OK, then check the SSISDB 'All Executions' report (assuming the package is deployed to SSISDB). It should tell you which component the package fails on.

Is the error happening in SSDT? If so, you should be able to see from the Output window exactly which component is causing the error.

Is your Expression task empty? Maybe...

I suspect that the problem does not occur when the update is performed, instead, the column does not exist and therefore the error occurs at parse time, just before execution.

There...

In my opinion, this is something which you should definitely fix.

But if you use

SELECT DISTINCT cols

rather than

SELECT cols

any duplicate rows will be removed.

There is no need to use script tasks to set the value of variables - use Expression tasks for that.

If you want to set a variable to indicate a file's...

Please tell me you're not creating temp tables 1TB and larger.

@phil-parkin: do you mean use the "Foreach Loop Container"? Would I then have in the container Expression Tasks or a Script Task?

Yes, that's it.

Configure the 'Collection' node properties as follows:

...

My preferred way of handling such things is to use a FOREACH container. The contents of the (appropriately configured) FOREACH container will execute only if the specified file is found,...