4

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!

KASQLDBA
  • 7,159
  • 6
  • 27
  • 53
K09
  • 1,394
  • 13
  • 36
  • 59
  • 1
    Error codes in SSIS rarely provide value. The text of the errors is usually much more enlightening. It is unusual for only a single Error event to be raised. Generally, I see three Errors every time an error occurs in an SSIS package. One of them usually has information we can make sense of. What's provided is not one of those cases. "An OLE DB error has occurred" Ok. Is it a bad query for supplied parameters? Is it permissions? Is the server unavailable? – billinkc Aug 17 '15 at 16:11
  • building off what @billinkc said, since it is an OLE DB error, you're establishing connection and likely running into some sort of data issue. You should get an error running from SSDT and if you cannot read the text right click, select copy, and paste in notepad. – Dave Aug 17 '15 at 16:16
  • Getting this error now after changing 'Execute Package Task' connection manager... Error 0x80070005. Failed to create an instance of empty child package. The DCOM configuration or SQL Server Integration Services installation on your machine might be corrupted – K09 Aug 17 '15 at 16:18
  • Found this in the EventLog on the SQL Server where SQL Agent is running... The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {1A494196-5232-485F-9811-F6B64} and APPID {7C2D11F6-6EB4-4275-BB84-7AE2D19} to the user SERVERS\myServiceAC SID (S-1-5-21-4273396776-2545883197-365699706) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool. – K09 Aug 18 '15 at 10:05
  • I followed the below but no success... https://www.mssqltips.com/sqlservertip/3086/how-to-resolve-ssis-access-denied-error-in-sql-server-management-studio/ – K09 Aug 19 '15 at 14:37
  • What kind of permissions does 'ourserviceaccount' have on the server that the job is running on? Does it belong to the sysadmins group? – Queue Mann Aug 19 '15 at 19:46
  • What version of SQL Server is on the SSIS server and the server running SQL Agent? Do you have more than one version of SSIS installed on either server? What does your job definition look like? If you log in as the service account on the SSIS server and run the packages (via command line) - do they work? If so, log on to the Agent server and run the packages (via command line) – billinkc Aug 19 '15 at 19:53

2 Answers2

2

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
mouliin
  • 522
  • 1
  • 4
  • 15
2

Back in 2007 Andy Leonard posted an answer on the Microsoft forums that looks much like your problem in the following link. In 2013, Chris Johnson noted that the answer still worked for him.

https://social.msdn.microsoft.com/forums/sqlserver/en-US/118d30fb-6748-49f4-94a8-509bc1066d61/execute-package-task-says-login-failed-for-sa

Andy's post noted the similar error:

Error 0xC0202009 while preparing to load the package.
An OLE DB error has occurred. Error code: 0x%1!8.8X!.

Andy's post noted that the OP may have had the wrong sa password and asked him to verify his password was correct.

Once that was resolved, then quoting from his answer:

  1. Try setting the ProtectionLevel property on the Control Flow properties to either EncryptAllWithPassword or EncryptSensitiveWithPassword. You will need to supply a PackagePassword for this test as well.

  2. When you're done, rebuild and re-deploy the package. You will need to supply the password when executing the package. If this succeeds, the issue is with the Package ProtectionLevel.

  3. I recommend using Windows Authentication to connect to the server. (Chris Johnson indicated that using Windows Authentication was necessary.)

(This is research on my end, not a problem that I have personally experienced.)

RLF
  • 14,015
  • 2
  • 33
  • 47