none
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

    Question

  • Hi,

    We are trying to convert the Sql server 2008 R2 ETLs to Sql server 2012 with Project level connections.

    Currently , we are having around 31 ETLs and all the ETLS have been converted and are working fine.

    Only 1 ETl has around 4-5 connections (package level connections) and we have converted it to project level connections except for 1 package level connections

    Which is used to get data from an SP.

    We have not been able to convert the same and it is giving us the following error in SSIS :-

    [irwSP_GetPositionDumpReport [94]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "IRW" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [SSIS.Pipeline] Error: irwSP_GetPositionDumpReport failed validation and returned error code 0xC020801C.

    It is giving the following error in EventViewer:-

    The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

    {6DF8CB71-153B-4C66-8FC4-E59301B8011B}

    and APPID

    {961AD749-64E9-4BD5-BCC8-ECE8BA0E241F}

    to the user NT SERVICE\SQL Server Distributed Replay Client SID (S-1-5-80-3249811479-2167633679-2115734285-1138413726-166979568) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

    We have tried the following :-

    Activity

    Results

    Converted the Connection from Package level to Project level.

    Failed.

    Deleted the Components and recreated them and then tested

    Failed.

    Reconverted the 2008 R2 package once again and tested

    Failed.

    Ran the Sp from Management Studio with the same credentials as the Connection Manager

    Succeded

    Created another Solution and then tried to convert the package and tested

    Succeded

    Made some Registry changes as per some of the Forums

    Failed.

    Made few setting changes as per the some Forums

    Failed.

    Wednesday, November 21, 2012 1:04 PM

All replies

  • it is a security issue, just run the package using a proxy that has the proper rights.

    Not sure where "IRW" connects to.


    Arthur My Blog

    Wednesday, November 21, 2012 4:41 PM
  • Refer link for help on issue :-

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/9757aeeb-df54-4f67-9df2-c5aa42502007

    http://blogs.msdn.com/b/ramoji/archive/2009/03/10/the-acquireconnection-method-call-to-the-connection-manager-failed-with-error-code-0xc0202009.aspx


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, November 21, 2012 5:41 PM
  • I forgot to mention that IRW connection is a Sql Server Connection and it being an external database server we do not have any control over the same. And they cannot provide us Windows auth, as our organization is different.

    Therfore the above blog cannot work in my case.

    I have already tried the follwoing :-

    DelayValidation to be set true.

    Run64bitRuntime set to false.

    Setting TransactionOption to be Not Supported as well.

    Is there someother workaround for the same? Is there any other settings that I could change and it will work ?

    Thursday, November 22, 2012 6:30 AM
  • That would mean you are connecting uisnG SQL Server Authentication to the EXTERNAL SERVER.

    Could you confirm that you can connect to the SQL server from Management Studio and the user credentials work?

    As can be seen from you logs the package is trying to connect using NT SERVICE\<Account Name> which pacakge variables are not set properly i.e. as its a mutli package scenraio the child package varaibles are not getting passed or the configuration file on the child package is not getting assinged properly.


    Abhinav
    http://bishtabhinav.wordpress.com/

    Thursday, November 22, 2012 11:30 AM
  • Yes, When I Connect to the Sql Server from Management studio , the user credentials work.

    The funny Part is that when I create a completely new Solution and add this SSIS Package , then too the Credentials work.

    But, the basic idea is too have one solution with all the packges and have project level connections so it is easily configurable , then it does not work. We tried with Windows Auth for development Environment and it works.But we cannot have Windows Auth for Production Environment and it fails for Sql Server Auth even in Dev Environment , Really baffling.When I put in my credentials in Sql Server Auth and Test Connection , it says Connection Succeeded, So the Credentials are right.

    I have made some changes in the Registry as been told in the Forum for Dcom components and the issue in EventViewer seems to be resolved.

    But , the package is still failing and I am getting the following error:-

    [OLE DB Source [143]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "IRW" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    [SSIS.Pipeline] Error: OLE DB Source failed validation and returned error code 0xC020801C.

    Thursday, November 22, 2012 11:45 AM
  • Your error VALIDATION FAILED which is cz your dealayValidation proeprty is not set properly.

    However per my last reply dod you confirm the child package varaibles are set properly try seeing the username passsword as set on the child package using a script task.

    As can be seen from you logs the package is trying to connect using NT SERVICE\<Account Name> which pacakge variables are not set properly i.e. as its a mutli package scenraio the child package varaibles are not getting passed or the configuration file on the child package is not getting assinged properly.



    Abhinav
    http://bishtabhinav.wordpress.com/

    Thursday, November 22, 2012 11:49 AM
  • However per my last reply dod you confirm the child package varaibles are set properly try seeing the username passsword as set on the child package using a script task.

    As can be seen from you logs the package is trying to connect using NT SERVICE\<Account Name> which pacakge variables are not set properly i.e. as its a mutli package scenraio the child package varaibles are not getting passed or the configuration file on the child package is not getting assinged properly.



    My DelayValidation Property is set to true.

    By Child Package Variables do u mean Package Connections (But if yes, then we have already converted it to Project Level Connections. If no, then could u please specify what PAckage Variables are? IRW is a simple Conection which executes No Parameter SP. ) 1 more query I would like to ask is how did u know looking at the logs that the package is trying to connect to NT SERVICE\<Account Name> Plus how do u check if the config file on the Child Package is getting assigned properly ? Currently I am not using any specific Package Configuration file. Please be more specific and pardon my limited SSIS knowledge.

    Thursday, November 22, 2012 12:03 PM
  • Forget about everything;While on the machine where the error exists try connecting to the "IRW" connection. E.g. you can create .UDL file and set it similarly up to connect as in the package (same provider, and all that juzz).

    Cannot connect either? And you have successfully confirmed that you have a security issue.


    Arthur My Blog

    Thursday, November 22, 2012 9:11 PM
  • I tried creating a connection with .UDL and the connection is created successfully.It could not be a security issue.

    If I create a new solution and add this 1 packge to the NEW solution , it works fine with the same SQL Auth. No issues.

    But, the existing solution in the package the "IRW" Connection says Tested Successfully. But while you execute it provides an error:-

    [OLE DB Source [143]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "IRW" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    [SSIS.Pipeline] Error: OLE DB Source failed validation and returned error code 0xC020801C.

    Like a I said , it is a bit baffling.

    I have even dropped and recreated the connection as well as the component, but to no avail.

    The Client expects a single solution for all the packages, I cannot provide them 1 Solution with just 1 package and the rest in another solution.

    Friday, November 23, 2012 9:33 AM