February 9, 2009 at 1:13 pm
I created a ssis package with the sql server export wizard.
The package exports from a query to a txt-file.
The query is a join over 2 tables on 2 different servers.
I use [server].[database].[schema].
in the query.
Just exporting works just fine. But executing a job containing the created ssis packages gives me the following errors:
02/09/2009 17:37:40,exportopenstaandeposten,Error,1,NDIMS-DB13,exportopenstaandeposten,exportopenstaandeposten,,Executed as user: NDIMS-DB13\SYSTEM. ... 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:37:40 PM Error: 2009-02-09 17:37:40.63 Code: 0xC0202009 Source: Data Flow Task Source - Query [1] 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: "Login failed for user 'NDIMS\NDIMS-DB13$'.". End Error Error: 2009-02-09 17:37:40.63 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - Query" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2009-02-09 17:37:40.63 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2009-02-09 17:37:40.63 Code: 0xC0024107 Source: Data Flow Ta... The package execution fa... The step failed.,00:00:00,0,0,,,,0
What can be the problem here???
Thanx in advance
Elsbeth
February 9, 2009 at 4:11 pm
It looks like a login failure one one of the servers. Is the login 'NDIMS\NDIMS-DB13$' you or the SQL Server Agent account? Does it have access to both servers? Are you using a linked server between the two servers?
Greg
February 10, 2009 at 1:23 am
I assume the "$" means that it is the user that is running the job?
In that case it has access on both servers. I didn't use a linked server, I used SP_ADDSERVER
and sp_serveroption.
Now I also with a linked server, but the result is the same.
February 10, 2009 at 2:41 am
try changing the protection level on the package...sometimes works
February 10, 2009 at 4:46 am
Nope that doesn't work.
I don't understand Login failed for user 'NDIMS\NDIMS-DB13$'."
Where does it get this information?
February 10, 2009 at 9:04 am
I don't know what "$" means. I asked who the login belongs to because, if the connection manager in the package specifies Windows authentication, it will use the credentials of the login running the job i.e. the SQL Server Agent. Since you say the account has access to both the local and remote database, I'm not sure what is causing the problem.
Greg
February 10, 2009 at 9:20 am
(Login failed for user 'NDIMS\NDIMS-DB13$'.". )
When you are running packages with a Job it runs in the context of SQL Server Agent permissions. And if you are not using two SQL Server the account used to create the package must also be an admin, you can create a proxy account for the agent but the package owner must be admin.
So make sure the Agent is admin and the failed login must also be admin.
http://support.microsoft.com/kb/918760
Kind regards,
Gift Peddie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply