July 29, 2010 at 2:44 pm
Hi I am trying to resolve an error I am getting when attempting to run a SSIS package I have recently copied to a new server. Some back ground info first this package is running successfully on a different server currently and I have made a copy and placed it on a new larger server. I have just been told that the new server is running windows operating system in 64 bit and the other server is running 32 bit . I mention that because as I was searching for a solution it seemed this info came up in some of the forums unfortunately none of them provided an adequate solution for me. I have narrowed the problem to a specific process which connects to a SQL 2000 box for one of my extracts (all other processes are coming from SQL 2008 and they all appear to be fine). During set up of the connection manager object the test connection button shows when clicked that the object is properly configured with the correct credentials to connect to the data source but once I execute the task which uses this object the process fails at the validation step with the following error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyServer\MySQL2000.MyDB.sa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Any assistance would be greatly appreciated.
August 5, 2010 at 4:51 pm
Can you run the package in bids? can you do a preview from the data source component?
how are you connecting, SQL Auth or windows?
what type of connection manager is it? OLE, ADO.net, etc...
A good way to check this is to run BIDS on the server (if you're allowed to do that) and open the package. See if works that way. Not familiar with connecting to SQL 2000 but I assume an OLE connection for SQL Server would work with 2000.
If SQL authentication make sure the password is being retained in the package encrypt sensitive with password and make sure the password was entered correctly when setting up the job.
If it's windows check the account the package runs under. this may have changed with the new server and may not have access to the SQL 2000 box.
Another check is to launch SSMS from the server and manually connect to the SQL 2000 box using the same credential as the package. You can do a runas and launch mgnt studio as the account that the package runs under.
hth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply