0

While searching for the solution, I have followed this link.

I have executed the package successfully with EXEC [SSISDB].[catalog].[create_execution]. Instead of either true or false for @use32bitruntime this command is executing well.

But if a server agent job is created with the same package, showing the below error.

The AcquireConnection method call to the connection manager ODBCManger failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

How to solve this . . .

CSR
  • 770
  • 1
  • 14
  • 30
  • It could be due to permission issues to account which SQL agent running Under. any other SSIS jobs running in Agent ? – Ven Feb 08 '18 at 11:50
  • SQL server agent is log on as 'NT Service\SQLSERVERAGENT'. This is the first job created using the SSIS package in SSISDB. – CSR Feb 08 '18 at 11:51
  • The user account scheduled to run this may not have correct permissions, did u tried using {SA} account instead – Ven Feb 08 '18 at 11:55
  • In order to roll back changes (after a try with other accounts), we don't have the default password for 'NT Service\SQLSERVERAGENT'. – CSR Feb 08 '18 at 11:56
  • I mean try changing owner of job from the job properties to SA, if it doesn't work. The ideal solution will be to run it in windows scheduler – Ven Feb 08 '18 at 12:01
  • Run it as a different user which has permissions to access your folder. – SqlKindaGuy Feb 08 '18 at 12:09
  • There wont be any password for 'NT Service\SQLSERVERAGENT', when u have to rollback u should leave password field blank and apply – Ven Feb 08 '18 at 12:15
  • I have set owner of the task to `sa`, but the result is same. – CSR Feb 08 '18 at 12:43
  • I have tried and tested it with a BCP utility by changing sql services account, it worked out for me and i have rolled back without having a password – Ven Feb 08 '18 at 13:01
  • Are you able to execute any SQL script using `Agent `? – Ven Feb 08 '18 at 13:08
  • Yes, before creating this question in SO, i have created the task with the TSql statements i have used to run in SSMS editor, but the result is failed. Whatever we do, running SSIS package via job is failed. I have tried a different query like a sample select statement, which is worked fine. Only calling the SSIS package script is failed. The package is not from the folder, it is in the SSISDB. – CSR Feb 08 '18 at 13:20
  • 1
    Thats clearly permission issues, Best practice described to run `sql services `in a domain account to be efficient and secure. I guess you woudn't want to change any account settings for `sql Agent`. The best approach would be to run in scheduler using `command line`, Let me know if u need any help with it – Ven Feb 08 '18 at 13:26
  • I have created another task from the package using file system. But results the same error. – CSR Feb 08 '18 at 13:26
  • It wont execute as its clearly permission issue, and also as another try keep your account permissions same as `sa` – Ven Feb 08 '18 at 13:29
  • 1
    @B House Thank you for your time. Its resolved finally. It is due to permission issue as you mentioned. It is not easily identified because the system is connected to VPN. Thanks a lot :) – CSR Feb 08 '18 at 13:33

0 Answers0