update the tables in to two server using a single ssis package
i have 2 servers server A and Server B.i have written a package in server A.i want to update the tables in both server A and B.when i execute the package from BIDS it is working fine when i schedule the package in agent job i am getting the below error.
Executed as user: RICRDWDEVSYSTEM. ...ckage Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:25:07 AM Error: 2012-04-03 09:25:23.00 Code: 0xC0202009 Source: SAPNew Connection manager "RICRDW.crdw.dbo" 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 Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'GTKRICRDWDEV$'.". End Error Error: 2012-04-03 09:25:23.00 Code: 0xC00291EC Source: Truncate Import Tables (RICRDWcrdw) Execute SQL Task Description: Failed to acquire connection "RICRDW.crdw.dbo". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:25:07 AM Finished: 9:25:23 AM Elapsed: 15.656 seconds. The package execution failed. The step failed.
Software/Hardware used:
sql server 2005 and BIDS
Executed as user: RICRDWDEVSYSTEM. ...ckage Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:25:07 AM Error: 2012-04-03 09:25:23.00 Code: 0xC0202009 Source: SAPNew Connection manager "RICRDW.crdw.dbo" 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 Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'GTKRICRDWDEV$'.". End Error Error: 2012-04-03 09:25:23.00 Code: 0xC00291EC Source: Truncate Import Tables (RICRDWcrdw) Execute SQL Task Description: Failed to acquire connection "RICRDW.crdw.dbo". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:25:07 AM Finished: 9:25:23 AM Elapsed: 15.656 seconds. The package execution failed. The step failed.
Software/Hardware used:
sql server 2005 and BIDS
ASKED:
April 4, 2012 10:04 AM
UPDATED:
April 5, 2012 4:57 PM
Answer Wiki:
Your SSIS package should have both server/database combinations as 2 different connections
Make sure the profile used to execute the package has update/insert permissions to both server/database/table
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
