July 29, 2008 at 4:28 pm
Hi, I hope someoen can help me with this issue.
Background of issue
I have two named instances on machine running sql 2005 Enterprise.
I have created a database on both instances for testing an SSIS package.
The package I have created has I Data flow task .The two simple Data flow tasks exist.
The process is get table contents from a tale in the second instanvce and polulate the a table in the first instance.
The package work fine if SSIS and after inporting it into the SSIS database.
The problem occurs when I try to run it as a SQL Agent job and I get the following error. The error sounds like it can't find the database that does exist. Does anyone know what could be wrong?
thanks in advance.
HISTORY ERROR LOG
Executed as user: TEST\SQL2005USER. ...n 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 23:25:05 Error: 2008-07-29 23:25:08.00 Code: 0xC0202009 Source: Data Flow Task Customers from RemoteDatabase on Instance 2 [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09. End Error Error: 2008-07-29 23:25:08.02 Code: 0xC02020E8 Source: Data Flow Task Customers from RemoteDatabase on Instance 2 [1] Description: Opening a rowset for "[dbo].[customer]" failed. Check that the object exists in the database. End Error Error: 2008-07-29 23:25:08.04 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Customers from RemoteDatabase on Instance 2" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2008-07-29 23:25:08.05 Code: 0xC004700C Source: Data Flow Task DTS.P... The package execution fa... The step failed.
July 29, 2008 at 7:44 pm
It looks like the agent service does not have required access to both the instances...make sure that the account on which the sql server agent runs, has all the rights required to access both the instances....or create a proxy account on agent service which has all the rights....
July 31, 2008 at 2:00 pm
make sure that you also have remote access enable on the sac console. 😎
"We never plan to Fail, We just fail to plan":)
August 2, 2008 at 4:46 pm
ssismaddy (7/29/2008)
It looks like the agent service does not have required access to both the instances...make sure that the account on which the sql server agent runs, has all the rights required to access both the instances....or create a proxy account on agent service which has all the rights....
I have managed to get the package working with a schedule by creating a Credential on the first server tha uses the administrator login. If i copy the administrator to a new iuser account it fails. I would prefer not to use the admin account. Any suggestions on what i could be missing?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply