December 23, 2009 at 7:18 pm
Background... I have 8 jobs running a single *.dtsx file in SQL Server 2005 Jobs.
Each *.dtsx does:
* 1 - updates a pkg log table (archive server) with start date
* 2 - delete recs from archive server where data changed since in source server
* 3 to 42 - 40 data pump tasks
* 43 - updates a record log table (source server) with processed=1
* 44 - updates a pkg log table (archive server) with end date
The data pump tasks access a source database (on server A) & writes it to a destination database (on server B) for archival purposes when data is a few yrs old. Then the data has to be removed from server A but not being done in this. So essentially they should have the same structures but just have different database names & are on diff servers.
This was setup by someone else who has since left. I noticed these jobs have been failing for quite some time. But am having trouble getting them going again.
Opened one *.dtsx file using SQL Server Business Intelligence Development Studio & fixed many errors/cautions to do with added/deleted/modified columns. However it still fails, error not very descriptive.
I did notice however when I click on each of the 2 connections in Connection Manager, it shows the username & a blank password with the 'save password' box ticked. If I click 'test connection' it fails, so I enter the password & 'test connection' succeeds so click OK (assuming this saves it.
If I re-open it there's a blank password & if I click 'test connection' it fails. If the password is actually still being retained & that it is a 'by design' issue, then how come the *.dtsx file run in a job fails on the first step (tbl_Package_Log has a very old date in it)?
DELETE FROM tbl_Package_Log
INSERT INTO tbl_Package_Log(StartRun) SELECT GETDATE()
If I double click the *.dtsx file in Windows Explorer I have the option to Execute it from there (pkg source: File system, pkg:.... path/name of my *.dtsx file), it runs through a heap of validation stuff appears to finish after 3mins, with
Warning: SSIS Warning Code DTSerrors & DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed(1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
But how?
Scrolled up & down in the screen log but cant see the 3 errors.
It DOES however succeed in setting tbl_Package_Log.StartRun but tbl_Package_Log.EndRun is set to NULL so final step did not succeed:
UPDATE tbl_Package_Log
SET EndRun = (SELECT GETDATE())
Any help would be greatly appreciated, thanks 🙂
* How do I go about being able to troubleshoot it please?
E.T.A.
Just found where to change MaximumErrorCount - select the task to view/edit it's properties.
Running the package manually...
* warning occurs step 42 (last data pump task) = 3 errors, so changed MaximumErrorCount to 5.
* ran again
* warning occurs step 44 (final task - as shown above) = 3 errors, so changed MaximumErrorCount to 5.
* ran again - shows after step 44 validation complete that it's failing : errors raised (3) reached max allowed (1).
- transfer data
* validation has started
- 01 - Log Package Run Start Time
- 02 - Delete Existing Data that changed
- 03 - DTSTask_DTSDataPumpTask_03 - [tablename]
... etc...
- 42 - DTSTask_DTSDataPumpTask_42 - [tablename]
- 43 - Update processed flag
- 44 - Update EndRun (Package Completed)
* Validation is completed
* Start, 1:42:32 PM
* Validation is started
* Validation is completed
* Start, 1:43:13 PM
* Warning: SSIS Warning Code DTSerrors & DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed(1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.[/color]
* Finished, 1:44:29 PM, Elapsed time: 00:00:00.000
* Finished, 1:44:29 PM, Elapsed time: 00:01:57:561
So what is it doing after step 44? there's no descriptive error messages, so dont know what errors they are nor how to fix as dont know what is failing.
January 7, 2010 at 7:24 pm
BUMP.... anyone?
January 8, 2010 at 6:09 am
If you wish to store login credentials within the package then you have to set the 'ProtectionLevel' package property to something other than 'DontSaveSensitive'
I normally set the 'ProtectionLevel' to 'EncryptSensitiveWithPassword', then specify a 'PackagePassword' before saving the file. The password then has to be supplied when either running or opening the package. Obviously the password must be kept in a safe place.
I'm not sure whether this will help you resolve the issue as it is possible that the credentials are stored in a .dtsxConfig file, but it would explain the issue you are having regarding the credentials not being saved.
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply