Sql-server – when running SSIS package with Windows Auth it works but with SQL Auth it fails

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 could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

• SQL Job with one Job Step
• The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

• an instance-level Credential
• a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -