October 10, 2011 at 10:27 pm
I have created a pcakge in SQL Server 2005 on a server using remote desktop connection.The db is present on this server itself.So in connection manager I am using WIndows Authentication.
Now I am trying to run the same package on my local machine. For this I created a user in Db security which had access to all the necessary dbs. I tested by logging with this user in SSMS. It worked fine.
In my local m/c package,i have modified all the connection mgrs with SQL Authentication with this new user credentials. But when I am running the package it is failing randomly at a diffrent task evrtime it is run.
Error msg is :
[Lookup [1420]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SPIN 2" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Can anybody suggest something to resolve this?
Thanks
October 10, 2011 at 10:57 pm
Just to be clear, you are using sql authentication, right?
also, how are you running the packages, from BIDS? Dtexec?
How are you configuring the password? External config file? Saved password in the package?
Also, there should have been an additional error above the one you posted. Can you double check?
October 10, 2011 at 11:06 pm
In my local machine, i am using SQL Authentication.
Running the package manually from BIDS.
I am just setting my password presently in my connection managers,package configurations are not enabled.
Yes,I've saved the password too.
And there is no additional error given. In Execution Result, you can see "Progress: Validating - 28 percent complete" and then the error.
October 11, 2011 at 8:23 am
If you go into the connection manager properties for SPIN-2, and verify the connection info and username/password then hit "TEST CONNECTION", does it return a message saying the test was successful?
If yes, then you could perhaps try just deleting the connection manager and re-creating it. Sometimes there are issues when you copy things like that from one machine to another.
If the test fails, then it probably means your machine isn't properly able to talk with the server.
October 11, 2011 at 4:50 pm
Typically, that error happens when ssis can't decrypt with the user key. If you aren't getting that error, then enable verbose logging and try again.
are you current on patches to sqlncli and bids?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply