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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics