We have SSIS running on its own server under account 'ourserviceaccount'.
We then have SQL Agent running on a separate server under the same account.
I have set up a job that calls an SSIS package, which in turn calls a number of child packages.
However, I get this error…
Error 0xC0202009 while preparing to load the package. SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x%1!8.8X!.
The job is run as the same service account credentials.
In the parent package that calls the child packages, I have set the connection manager to Windows Authentication.
What step am I missing?
Thanks!
Best Answer
I had similar problem earlier and what I found is because the creator of the Package and executor of Package were different user it was having some issues.
To get around this issue, I had to protect the package with password and I used that password to access the package in my Job.
You should give that a try, I hope it helps.
Also, is your package doing any distributed transactions? You may need to set authentication from Services for distributed transaction.