February 6, 2009 at 8:06 am
Hi all,
I'm a newbie in SSIS packaging but I've managed to create a package that works fine from my local PC. There's nothing fancy about the package. What it does is:
* Delete all data from a Remote Server
* Read Data from local server and insert to Remote Server
Like I said everything works smoothly when I run the package from my local PC but when I schedule it in the SQL Server Agent, I run into problems. The error I get is the following:
Message
Executed as user: . ...42.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 15:54:19 Progress: 2009-02-06 15:54:19.75 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2009-02-06 15:54:19.98 Source: Data Flow Task Validating: 50% complete End Progress Error: 2009-02-06 15:54:40.95 Code: 0xC0202009 Source: OrderData Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2009-02-06 15:54:40.95 Code: 0xC020801C Source: Data Flow Task Destination - OrderData [43] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER... The package execution fa... The step failed.
I've had some problems along the way and managed to figure out that I needed to create a "Package Configuration" file (.dtsConfig).
I've set the "ProtectionLevel" of the package to "DontSaveSensitive" and added the Connection String to the config file. I saw that no password was included in the Connection String (while opening the config file in Notepad) and manually added "Password= ;" to the Connection String, but the same error occurs.
Does anyone have any idea?
February 6, 2009 at 2:28 pm
Hi,
From the error msg it seems like the connection timed out. You might have to manually configure the timeout by adding it to your connection string.
Are you using SQL or Windows authentication in your SQL connection to the remote server? If you are using Windows authentication I would suggest you ensure that the SQL Agent service account have the appropriate permissions on the remote server.
Hope this helps.
February 6, 2009 at 3:41 pm
Hi,
I am using SQL Authentication.
It is possible that it times out, however I find it quite unlikely. And the reason why is because when I run it from my local PC, it doesn't take much more than a second to run the entire package. I can also connect to the remote server, via Management Studio, on the machine where the package is run and there it connects instantly to the remote server. So I don't see any reason to why the connection times out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply