SSIS Fails intermittently when scheduled as a SQL Agent Job.
I have several SSIS jobs that I have created and they operate just fine when running manually or even when the job is scheduled.
Lately, I have been seeing some intermittent failures in these jobs and am having a difficult time in understanding how to resolve this issue.
All Jobs runs consistently overnight with no issues and start to fail intermittently as the load on the server increases.
Attached is the output from a successful job and a failed job.
Any Suggestions?
--Successful Run
Date
4/8/2011 9:45:01 AM
Log
Job History (WFM_TCSDATA_EXP_LAS_15)
Step ID
1
Server
DNC-UCCECUIS04
Job Name
WFM_TCSDATA_EXP_LAS_15
Step Name
WFM_TCSDATA_EXP_LAS_15
Duration
00:00:36
Sql Severity
0
Sql Message ID
0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted
0
Message
Executed as user: CCT\CUISServiceAcct. Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:45:01 AM DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 9:45:01 AM Finished: 9:45:37 AM
Elapsed: 35.906 seconds. The package executed successfully.
The step succeeded.
-- Failed Run
Date
4/8/2011 9:15:00 AM
Log
Job History (WFM_TCSDATA_EXP_LAS_15)
Step ID
1
Server
DNC-UCCECUIS04
Job Name
WFM_TCSDATA_EXP_LAS_15
Step Name
WFM_TCSDATA_EXP_LAS_15
Duration
00:00:44
Sql Severity
0
Sql Message ID
0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted
0
Message
Executed as user: CCT\CUISServiceAcct. ....00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:15:00 AM Error: 2011-04-08 09:15:43.11
Code: 0xC001401E Source: DNC-UCCECUIS04_WFM_TCSDATA_EXP_LAS_15 Connection manager "FileLocation"
Description: The file name "\\10.184.124.14\d$\WFM\V2\EXP_LAS_15" specified in the connection was not valid.
End Error Error: 2011-04-08 09:15:44.30
Code: 0xC0202070 Source: DNC-UCCECUIS04_WFM_TCSDATA_EXP_LAS_15 Connection manager "DataFileLocation"
Description: The file name property is not valid. The file name is a device or contains invalid characters.
End Error Error: 2011-04-08 09:15:44.32
Code: 0xC0202010 Source: Data Flow Task - Get Rows DataFileLocation [1096]
Description: No destination flat file name was provided. Make sure the flat file connection manager is configured with a connection string. If the flat file connection manager is u...
The package execution fa... The step failed.
Steve
April 8th, 2011 1:07pm
Executed as user: CCT\CUISServiceAcct. ....00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
..
.
.
.Description: No destination flat file name was provided. Make sure the flat file connection manager is configured with a connection string. If the flat file connection manager is u...
Q1: what is you r source data format ? Excel Text file ? what is it
Q2: it say no destination FF name was provided ? what section is that and what it doing?
Q 3: how are setting you r Job to call the packages ?
Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 1:20pm
I appreciate the help. I understand what the error message is saying. The question I have is the job runs 48 times per day. 30 times a day it is successful and writes to the flat file with no issues. The other 18 times it is not
successful. Steve
April 8th, 2011 1:57pm
This portion looks odd "\\10.184.124.14\d$\WFM\V2\EXP_LAS_15" I think to use a UNC path to a share is not very reliable, so in short, you must have some sort of blip at times with the file availability. It is a reliability issue.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 2:06pm
OK steve
How long oes each run takes? and ArthurZ maybe right.Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
April 8th, 2011 2:34pm
Is Connection "FileLocation" dynamic ? , if yes, then go to properties of this connection and then set DelayValidation
property to TRUEThanks http://dwhanalytics.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
April 9th, 2011 8:52pm
This is a permission issue. check the job execution user account has full permission to the parent folder and sub folder. This error is occuring only with flat file connection manager. I think MSFT should look into this problem and provide meaningful error
message.
Because, In my org I am using a temp shared forlder to store and read data from all packages. But, packages that are failed with this error are using flat file connection. otherwise, rest of my packages that are using excel, dbf, mdb, etc
are doing good.Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com
May 18th, 2012 3:07pm