March 25, 2004 at 9:25 am
When trying to run a Scheduled DTS Package using a Job I get the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1 DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147220413 (80040443) Error string: Unable to connect to source server for Transfer. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220413 (80040443); Provider Error: 0 (0) Error string: Unable to connect to source server for Transfer. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). Error source: Microsoft SQL-DMO (ODBC SQLState: 08001) Help file: SQLDMO80.hlp . The step failed.
Source Server: DBSRVR
Right click SQL Server Agent - choose Properties
General Tab - SQL Agent Service Startup account: DOMAIN\Administrator
Job System Tab – non-SysAdmin job step proxy account: unchecked
ReSet Proxy Account: DOMAIN\Administrator
DOMAIN\Administrator has SysAdmin dB Role
sa has SysAdmin dB Role
Destination Server: GP2
Right click SQL Server Agent - choose Properties
General Tab - SQL Agent Service Startup account: DOMAIN\Administrator
Job System Tab – non-SysAdmin job step proxy account: unchecked
ReSet Proxy Account: DOMAIN\Administrator
The Job and DTS Package both reside on the GP2 server.
The Package has one task: Copy SQL Server Object Task wherein it is attempting to copy 4 tables from one database on the DBSRVR server to a backup database of the same four tables on the GP2 server.
NOTE: This DTS Package runs fine from SQL Enterprise Manager but will not run successfully as a Job.
The Job on the GP2 server has the following characteristics:
Target Local Server radio button is checked
Source: gp2
Category: [Uncategorized(Local)]
Owner: sa
The Source dB owner is dbo with sa login as is the destination dB. What am I missing?
March 25, 2004 at 9:54 am
If you login as the SQL Server Agent account and run EM, does it still run? It's a connection problem of some sort. It's like the source server isn't running when the agent loads the package. How is the server connection set, but name?
March 25, 2004 at 3:58 pm
I've tried logging onto the GP2 server as DOMAIN\Administrator and tried to run the job while on the server and it still fails. Is there anymore informatin I can give you to help me figure this out?
March 25, 2004 at 9:36 pm
Are you using the MSDA provider for ODBC data sources instead of the OLEDB provider for SQL Server. If so your DSN connection properties are probably the culprit check them out with the ODBC data source adminstrative tool.
Peter Evans (__PETER Peter_)
March 26, 2004 at 12:45 pm
Sorry folks, all's well with SQL Server, I discovered via ping no less that my route table had an erroneous entry, did a route delete to take out the erroneous entry and all's well. I'm still curious as to how in world I could still connect via Enterprise Manager and perform the DTS task yet not be able to put that DTS task in a Scheduled Job when I wasn't able to ping the server on the network. In the route table were two entries, maybe it was just hit or miss that the network router was checking the correct route table entry and not the erroneous one put in by our network admin whenever I ran the DTS task and not when I tried to run the Job.
March 26, 2004 at 1:31 pm
I have seen this before on a different type of server. The router route tables seemed to round robin the effect and some folks got thru others did not, the server also had no issues seeing out due to this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply