Sql-server – SSIS – Setup OLEDB Connection to SQL Server Always On Listener

I would advice you to upgrade the package by following these steps

If after your upgrade you still run in to problems, it could be that your package is using components/drivers that are not available on 64bit. In that case the "easiest" work around would be to have your package use a 32bit runtime.

Open de Job properties and then edit the job step that is calling the SSIS package.

Go to the Execution Options tab and check "use 32bit runtime"

It adds the switch /X86 to the Dtexec execution string. (please note that this switch will be ignored if you directly run it from the command line.)

However, be aware that in that case, you are running the package in 32bit while not making use of the 64bit benefits. This shouldn't have to be a problem, just something to keep in mind.

I was not able to get a more specific error message than: "Unspecified Error".

I solved the problem by improving the performance of the queries used.

There was a particular expensive proc which took 10 minutes to run. It joined 8 tables, one of which had 13m rows, had 5 where clauses, and produced 13 million rows. It was being run almost 20 times in the course of the various SSIS sub-packages. Sometimes it would be run 2-3 times in a single package, producing the same set of results, but obviously table-scanning each time.

So I created a table to store the results of that expensive query, made a scheduled job that runs daily at midnight to populate it once. Then i just joined the results of that table in each place where previously the expensive proc was being run.

Now the job runs in 25 minutes consistently (instead of 3 hours), and i haven't seen a single failure in the so far 8 runs it executed.

So I suspect that the under-the-hood issue with the "Unspecified Error" was an out of memory exception that may or may not have been linked to the fact that the query was being executed over VPN linked server connection.