Search code examples
sql-serverssissql-server-2014

run SSIS Package from SQL Agent

I am trying to create a new job to run a SSIS package automatically.

I succeed to execute run the package from SSDT but when I try to run it from sql Agent, it generate the following error:

 Source: Package      Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

I try it with and without cheking the box use the 32 bit runtime. The sql server and SQL Agent are 64 bit

when I changed the protection level to DontSaveSensitive, I get The following error:

Executed as user: VG-DATA2\vgadmin. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  22:54:19  Error: 2018-04-22 22:54:20.03     Code: 0xC0202009     Source: Package Connection manager "vg-data2.westeurope.cloudapp.azure.com.WT_Delivery.vigicolis"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Échec de l'ouverture de session de l'utilisateur 'vigicolis'.".  End Error  Error: 2018-04-22 22:54:20.03     Code: 0xC00291EC     Source: Tâche d'exécution de requêtes SQL Execute SQL Task     Description: Failed to acquire connection "vg-data2.westeurope.cloudapp.azure.com.WT_Delivery.vigicolis". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  22:54:19  Finished: 22:54:20  Elapsed:  0.156 seconds.  The package execution failed.  The step failed.

Has someone an idea of this problem?

Thanks in advance

Solution

  • Adding to other answers, as you are running SQL and SSIS 2014, I would recommend switching to SSIS Catalogue and Project Deployment mode. By doing this you can solve your problem of storing connection password in Environments.

    Good point of this design is that sensitive passwords could be maintained by other people, say, support admins, not developers themselves. This is quite useful in production environments where developers have no access to.

    A good article on how to use SSIS Catalogs and use Environments for connectivity data