June 30, 2011 at 1:48 am
Morning All
Last week we had a requirement to move a database from one server to another, but keep the original database in place.
Eg. move DB1 on Server1 to DB1 on Server2
There was an SSIS package on Server1, which transfered two tables from DB1 into DB2 and all was working fine.
The SSIS package has now been changed to transfer the tables from DB1 on Server2 to DB1 and DB2 on Server1 to ensure consistancy between all three databases for reporting aspects.
Ever since this change, the package will not run via the job step but will run if you connect to SSIS via management studio and execute package
The step error is
Executed as user: DOMAIN\SQLAgentAccount. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 05:10:56 Error: 2011-06-30 05:10:57.00 Code: 0xC0202009 Source: Copy Data Source - TABLE1[1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09. End Error Error: 2011-06-30 05:10:57.00 Code: 0xC02020E8 Source: Copy Data Source -TABLE1[1] Description: Opening a rowset for "[dbo].[TABLE1]" failed. Check that the object exists in the database. End Error Error: 2011-06-30 05:10:57.00 Code: 0xC004706B Source: Copy Data SSIS.Pipeline Description: "component "Source - TABLE1" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2011-06-30 05:10:57.00 Code: 0xC004700C Source: Copy Data SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-06-30 05:10:57.00 Code: 0xC0024107 Source: Copy Data Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 05:10:56 Finished: 05:10:57 Elapsed: 0.39 seconds. The package execution failed. The step failed.
Can anyone shed some light on this?
Thanks
Ant
June 30, 2011 at 1:56 am
This was removed by the editor as SPAM
June 30, 2011 at 1:59 am
no, we use connection managers to define the source destination databases and yes the tables are the same on all three databases.
Just find it weird how it works running manually but not via the agent
June 30, 2011 at 2:08 am
This was removed by the editor as SPAM
June 30, 2011 at 2:14 am
yeah I have checked the connection managers, they are correct, all using windows authentication, all of the engine and agent acounts have SA access across the estate so no permission issues talking server to server
June 30, 2011 at 2:45 am
This was removed by the editor as SPAM
June 30, 2011 at 2:57 am
This was removed by the editor as SPAM
June 30, 2011 at 4:58 am
yeah the controls are secure, there are only two people who know where the passwords are, the place where they are is secured with no in or out access other than RDP and file copy abilities so that we can get onto the server, the server has a database which is encrypted by 256 AES using TDE, all the sensitive columns are encrypted at a column level, the backup is also encrypted using 256 AES
All the passwords are 64 characters long, mixture of upper, lower, numbers, symbols and no two characters can be the same side by side.
We have done a work around, re-creating the package from scratch has solved the issue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply