June 21, 2012 at 12:20 pm
Hi I am trying to run SSIS package in a job on daily basisi
1)Created and Tested the package for loading a data from Dev1 to Dev2 Server(Status:Successfull)
2)Now i want to keep the same Package which i created and tested in BIDS in a job.
3) I am failing with the following error
Date6/21/2012 11:00:00 AM
LogJob History (JOB)
Step ID1
ServerDEV2
Job NameJOB
Step NameJOB
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: DEV2\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 11:00:00 AM Error: 2012-06-21 11:00:00.93 Code: 0xC0202009 Source: JOB Connection manager "Conn1" Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAIN\DEV2$'.".
End Error Error: 2012-06-21 11:00:00.93 Code: 0xC020801C Source: Data Flow Task OLE DB Source [16]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Conn1" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-06-21 11:00:00.93 Code: 0xC0047017
Source: Data Flow Task SSIS.Pipeline Description: component "OLE DB Source" (16) failed validation and returned error code 0xC020801C. End Error Error: 2012-06-21 11:00:00.93
Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-06-21 11:00:00.93 Code: 0xC0024107
Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:00:00 AM Finished: 11:00:00 AM Elapsed: 0.64 seconds. The package execution failed. The step failed.
4)Am i missing any basic thing here.
Thanks!
June 21, 2012 at 12:31 pm
The following is the crux of the issue:
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAIN\DEV2$'.".
My first guess it that the user is what's running SQL Agent and that account doesn't have access to both servers.
June 21, 2012 at 12:54 pm
Thanks for the reply..
Can you be more clear. Please.
let me know how can i go forward on this
June 21, 2012 at 1:02 pm
I'm assuming SQL Agent on Dev2 is what's running the package. Take a look at the error log on Dev1. You'll likely see some login errors for "DOMAIN\Dev2$" and a reason for the login failure. Address that failure. If it's a failure because the account doesn't have access you have a couple options. You can have the SSIS package use different credentials in various ways (I'm not an SSIS expert so I don't know them off hand). An easier option as long as it's acceptable is to grant the account access on Dev1.
June 22, 2012 at 12:28 am
cfradenburg (6/21/2012)
I'm assuming SQL Agent on Dev2 is what's running the package. Take a look at the error log on Dev1. You'll likely see some login errors for "DOMAIN\Dev2$" and a reason for the login failure. Address that failure. If it's a failure because the account doesn't have access you have a couple options. You can have the SSIS package use different credentials in various ways (I'm not an SSIS expert so I don't know them off hand). An easier option as long as it's acceptable is to grant the account access on Dev1.
The strange part is that the package is executed using the account DEV2\SYSTEM - which I guess is the SQL Server Agent account - while the failed login was for user 'DOMAIN\DEV2$.
@sqlism: do you use any configurations in the package? Do you use a proxy to execute the package in the SQL Server Agent job?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 22, 2012 at 10:01 am
I am not using any proxy account.
Manually ran it in BIDS with my credentials and trying to set up the same package in JOB
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply