June 13, 2011 at 7:57 pm
Hi, I have a package that looks at a certain folder for an Excel file (.xls) and if the file doesn't exists, it won't run.
What I did is:
1. Create a package variable called FileName, with value using expression that uses the current date to build the filename, let's say the FileName value now becomes "Import_2011-06-14.xls".
2. I have a Script Task that has the FileName as ReadOnlyVariables, and in the script, I check if the file exists or not. I then use another variable ErrMsg as ReadWriteVariables, if file exists, I set ErrMsg = "" and if file does not exist, I set ErrMsg = "File not found."
3. I then build 2 precedence constraints both using Expression based on the ErrMsg variable, 1 reporting that file is not found, and then does nothing, the other continuing with the import etc.
4. I also have an Excel File Connection using the variable FileName as the ExcelFilePath, and set DelayValidation to True.
So, everything went OK when I run the package from BIDS, I can see, if the file doesn't exists, the package executes the task that reports that file not found, and if the file exists, it goes to the task that imports the file and continue to do the rest of the tasks in the package.
However, as soon as I import the package to my SQL2008R2 Integration Services, and set up a scheduled tasks in the SQL Agent, it always failed if the file does not exists, reporting that it cannot find the specified file on the import task. I traced the error and as far as I can tell, it validated the Excel File Connection and as soon as it sees the ExcelFilePath doesn't exists, it failed the validation and refuse to execute the package.
I've looked up the Internet but can't seem to find a way to circumvent this validation process. Does anyone know how to, or how I should handle this differently?
Thanks,
Ignatius
June 14, 2011 at 10:41 am
You can't prevent the validation, you can delay it. There is a flag on nearly every container that is DelayValidation (or something real close to that), I'm willing to be the flag is set to No..
CEWII
June 14, 2011 at 4:57 pm
I've always used a dummy file with the correct structure for situations like this.
June 14, 2011 at 4:59 pm
Charles Hottle (6/14/2011)
I've always used a dummy file with the correct structure for situations like this.
I do the same as Charles with this. Royal PITA but never really found an effective workaround.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 14, 2011 at 5:34 pm
Go to the event handlers tab, select the package root, create "OnPreValidate".
Add a Script Task, language C#, and paste the following main function:
public void Main()
{
Int16 iter = 0;
ConnectionManager c = Dts.Connections["MyConnection"];
System.IO.FileInfo s = new System.IO.FileInfo(c.ConnectionString);
while (iter < 1000 && !s.Exists)
{
++iter;
s.Refresh();
if (s.Exists)
{
Dts.TaskResult = (int)ScriptResults.Success;
return;
}
else
{
System.Threading.Thread.Sleep(5000);
}
}
Dts.TaskResult = (int)ScriptResults.Failure;
}
Change the connection name from MyConnection to the name from your Connection Manager that you're waiting on and you should be all set. This loops 1,000 times, sleeping five seconds in between until the file exists. If after all the iterations expire the file is still not there, it'll fail the PreValidation stage of the package resulting in the whole package failing.
June 15, 2011 at 12:11 am
Thanks for this Craig. Whilst useful, I don't think I can use it because there will be case where the file will not exist, and hence, rather than throwing an error, I have set the package to finish up without an error (so that it doesn't keep sending email for every day the file doesn't exist).
June 15, 2011 at 8:11 am
Iggy-SQL (6/14/2011)
Thanks Elliott for your reply. I have already set the DelayValidation to True, yet it still won't let me pass that Excel file connection complaining about non-existing file.
Iggy,
Per the BOL and my experience, you need to set the flag on each component you want to do delayed valdiation, including connection managers and likely data-flows using those connection managers. Basically setting it at the package level is insufficient. Let me know.
CEWII
June 15, 2011 at 12:05 pm
Iggy-SQL (6/15/2011)
Thanks for this Craig. Whilst useful, I don't think I can use it because there will be case where the file will not exist, and hence, rather than throwing an error, I have set the package to finish up without an error (so that it doesn't keep sending email for every day the file doesn't exist).
If you know when the file won't exist, you could always use a SQL Task to go around the process; the connection shouldn't be validated until it's needed if delay validation is turned on for everyone object which uses it. That is, if file is missing every Tuesday, SQL task which returns 1 or 0 if file should exist, use a Expression & Constraint to test the variable, on Success to flow to the file, and another Expression & Constraint to flow around when the file shouldn't be there.
If it's unknown when the file will be there or not, use the same code in a Script Task in the main package, create an onError handler (doesn't need an object in it, but a good place for logging), and set the Propagate variable in the OnError handler to False; this will keep the error from bubbling up to the parent object. Create an OnFailure/OnCompletion constraint to work around which ever object uses the connection. This way, the script task will error out, but the parent won't actually report it as a failure, and you can just bypass the objects which need that connection. Package completes reporting success even though the script task has failed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply