locked
failed to execute ssis package RRS feed

  • Question

  • The error message below shows, there is a failure on connection to the connection manager.
    I have checked and the user SSISLoader with the correct password can connect to the query analyser.
    Do you know if certain permissions should be given to this sql user?

    Thanks

    Microsoft (R) SQL Server Execute Package Utility
    Version 10.50.1600.1 for 32-bit
    Copyright (C) Microsoft Corporation 2010. All rights reserved.
    NULL
    Started:  13:24:51
    Error: 2012-01-10 13:24:51.35
       Code: 0xC0016016
       Source: 
       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.
    End Error
    Error: 2012-01-10 13:24:51.80
       Code: 0xC0202009
       Source: FXRates Connection manager "DBName"
       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 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'SSISLoader'.".
    End Error
    Progress: 2012-01-10 13:24:51.90
       Source: Send Mail Task
       The SendMail task is initiated.: 0% complete
    End Progress
    Progress: 2012-01-10 13:24:52.90
       Source: Send Mail Task
       The SendMail task is completed.: 100% complete
    End Progress
    Error: 2012-01-10 13:24:52.90
       Code: 0xC00291EC
       Source: Purge Existing Imported Data Execute SQL Task
       Description: Failed to acquire connection "DBName". Connection may not be configured correctly or you may not have the right permissions on this connection.
    End Error

    Tuesday, January 10, 2012 1:32 PM

Answers

  • Rather than don't save sensitive (which deletes all passwords in your packages), you can set it to Encrypt sensitive with password.

     


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    • Marked as answer by Eileen Zhao Tuesday, January 17, 2012 2:27 AM
    Tuesday, January 10, 2012 2:38 PM

All replies

  • This is one of the common issues.  When you put a connection string in your package that includes a password, that password is going to be encrypted.  By default it uses a key that is tied to the user that created the package.  Anyone other than that, including the SQL Server Agent job user, will not have access to that password.  You can change the package protection level, perhaps adding a password.

    Here are a couple resources.  Also, the FAQ has some discussion of this.

    http://msdn.microsoft.com/en-us/library/ms141747.aspx

    http://bi-polar23.blogspot.com/2009/04/ssis-and-package-protectionlevel.html


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    Tuesday, January 10, 2012 1:40 PM
  • Changed the protection level to do not save sensitive and still gave the same error.
    Tuesday, January 10, 2012 1:47 PM
  • It doesn't work, because the password is not kept at all.

    If I am working on a package with someone else, I use Encrypt with Password.  That means that everyone who opens the package will have to have the password.


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    Tuesday, January 10, 2012 1:57 PM
  • It doesn't work, because the password is not kept at all.

    If I am working on a package with someone else, I use Encrypt with Password.  That means that everyone who opens the package will have to have the password.


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    Not sure what you want me to do.
    What settings should I change?
    Thanks
    Tuesday, January 10, 2012 2:08 PM
  • Rather than don't save sensitive (which deletes all passwords in your packages), you can set it to Encrypt sensitive with password.

     


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    • Marked as answer by Eileen Zhao Tuesday, January 17, 2012 2:27 AM
    Tuesday, January 10, 2012 2:38 PM
  • Ok, I will try that.
    Thanks
    Tuesday, January 10, 2012 4:08 PM