none
Cannot acquire a managed connection RRS feed

  • Question

  • Dear All,

    I am very new to the SSIS package development so please point out my stupidity If I have done any. I am trying to ETL data from excel spreadsheet to SQL server using Excel data connections and OLE DB and ADO.Net data connection. I am using CDC task to monitor any changes done in the source excel file. I am doing it in a way same package extract information from the spreadsheet and import in to Server 1 (CDC enabled) and compared it to the same table hosted on another server 2 which is a production server.

    When I run this package on DTS it runs fine and I am able to generate Deployment Manifest file. When I try to run the same Manifest file I am getting below error

    Error: Failed to Decrypt an encrypt XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.

    Error: Cannot acquire a managed Connection from the run-time connection Manager

    I have change the package encryption level from "EncryptSensitivewithUserkey" to "EncryptsensitivewithPassword" and have added password to the PackagePassword it the package property and saved this project.

    when i add this package to the SQL agent job I am still getting below error message

    Message
    Executed as user: administrator. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  11:44:49  Error: 2014-12-04 11:44:52.10     Code: 0xC020801F     Source: CDC Control Task CDC Control Task     Description: Cannot acquire a managed connection from the run-time connection manager.  End Error  Error: 2014-12-04 11:44:52.10     Code: 0xC0024107     Source: CDC Control Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:44:49  Finished: 11:44:52  Elapsed:  2.215 seconds.  The package execution failed.  The step failed.

    Please help I am stuck on this issue from last 2 days now. If you need more info please let me know.

    regards

    JK

    

    Thursday, December 4, 2014 12:10 PM

Answers

  • Looks like you run the package in thee Agent for this purpose you should use the 'Rely on server storage for encryption' (ServerStorage) protection

    In the "EncryptsensitivewithPassword" scenario you need to provide the actual password by editing the command line in there:

    with adding switch /De <password> to it


    Arthur

    MyBlog


    Twitter


    • Edited by ArthurZModerator Thursday, December 4, 2014 2:54 PM
    • Marked as answer by JKhizer Friday, December 5, 2014 8:49 AM
    Thursday, December 4, 2014 2:53 PM
    Moderator