December 5, 2014 at 9:44 am
Hello,
Currently I am running a SSIS package scheduled daily at 7 A.M. It expects two feed files from two different folders.
The first step in my package will rename the input files in those folders to names which the package can understand. I have created two variables in my package to read the files with those file names. I used these variables in connection managers.
If any of these folders doesn't have input files when the package runs, the package will fail. Can someone please let me know how to make the package the run successfully even there is no input feed?
Any help is highly appreciated.
Thank you.
December 8, 2014 at 10:14 am
There are a few ways to prevent the package from failing when the file(s) are not there.
One is to change the package properties so that it reports success even when it fails. I don't recommend this though because even legitimate failures would report success.
Another is to use an 'event handler' on the data flow task. In the event handler, check the error code and override the failure if it is due to the missing file. That'd work, but it's a bit over complicated I think for what you are trying to accomplish.
I think the best solution in your case is to add a ForEach loop to your package. Set it to loop through files with your filename in the directory where your input file is. Put your data flow inside the loop. If no files are found, the data flow inside the loop is never run and you don't receive a failure.
That works great for a single file. If your two files are dependent on each other though, and you don't want to load anything if either file is missing, then you may have to approach it a little differently. You could add a ForEach loop for each file that sets your variable for each of the files. You then set a condition on your connection to your data flow that checks to make sure the variables have a value set. If they do not have a value, the data flow step will not execute and you won't receive a package failure.
I hope this helps.
December 8, 2014 at 10:23 am
sestell1 (12/8/2014)
There are a few ways to prevent the package from failing when the file(s) are not there.One is to change the package properties so that it reports success even when it fails. I don't recommend this though because even legitimate failures would report success.
Another is to use an 'event handler' on the data flow task. In the event handler, check the error code and override the failure if it is due to the missing file. That'd work, but it's a bit over complicated I think for what you are trying to accomplish.
I think the best solution in your case is to add a ForEach loop to your package. Set it to loop through files with your filename in the directory where your input file is. Put your data flow inside the loop. If no files are found, the data flow inside the loop is never run and you don't receive a failure.
That works great for a single file. If your two files are dependent on each other though, and you don't want to load anything if either file is missing, then you may have to approach it a little differently. You could add a ForEach loop for each file that sets your variable for each of the files. You then set a condition on your connection to your data flow that checks to make sure the variables have a value set. If they do not have a value, the data flow step will not execute and you won't receive a package failure.
I hope this helps.
... or, as first step, add a Script Task to check if the files exist. Use conditional logic to proceed if they do, or abort if they don't.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 8, 2014 at 11:00 am
As was mentioned before one of solutions is to add a script task to check if file exists.
If you decide to use that approach this may help:
1. create boolean variable S_FileExists.
2. create string variable U_FilePathandName and add path and name of your file there (for example C:\testfile.txt). Find property of that variable Expressions->Connection String and set property to @[User::U_FilePathAndName].
3. add script task from Toolbox. Add variable U_FilePathAndName as ReadOnlyVariable and variable S_FileExists as ReadWrite Variable
add the following code to script task:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_7929f9cf331c43239066b2a7abefaca3.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
if (System.IO.File.Exists(Dts.Variables["User::U_FilePathAndName"].Value.ToString()) == false)
{
var textToWrite = "";
Dts.Variables["User::S_FileExists"].Value = false;
textToWrite = "Cannot find file - " + Dts.Variables["User::U_FilePathAndName"].Value.ToString();
string caption = "File does not exist.";
string message = textToWrite.ToString();
DialogResult button = MessageBox.Show(message, caption, MessageBoxButtons.OK);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
If file does not exist it will show Pop-up Message. This is just to test how script is working.
You can use value of variable S_FileExist in your package design, in Precedence constraint.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply