CODEWITHSUNDEEP
c#ssis
skyline01
skyline01

Reputation: 2101

SSIS flat file exists -- script task fails when no file exists

I am working in SSIS (BIDS) under SQL Server 2008 R2. I have a package that imports a flat file to an OLE DB. Before the import data flow task, I have a script task (written in C#, not VB) to test if the file exists. I have 2 precedence constraints off of the script task. The first one is my success path (Evaluation Operation = 'Constraint' and Value = 'Success'), which goes to a data flow task. The second one is my failure path (Evaluation Operation = 'Constraint' and Value = 'Failure'), which goes to a dummy task (a SQL task), just so that the package doesn't fail when the file doesn't exist.

In debugging, I confirmed that, when the file exists, it goes all the way through the data flow task (as expected). However, when the file doesn't exist, the package fails; in particular, it fails at the first step (i.e., the script task). I don't know what I'm doing wrong. Below is my code for the script task:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_2f8cf79f6fe0443b9c09c453433a0258.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 (File.Exists(Dts.Variables["PRC_file_path"].Value.ToString()))
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

    }
}

Upvotes: 1

Views: 3636

Answers (1)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

As far as I can tell, it's behaving exactly as it is supposed to; it fails the script if the file doesn't exist.

I would use a variable instead to report the existence of a file.

public void Main()
{ 
string targetfile = Dts.Variables["PRC_file_path"].Value.ToString();

 if (File.Exists(targetfile))
            {
                Dts.Variables["file_exists"].Value = true;
            }
            else
            {
                 Dts.Variables["file_exists"].Value = false;
            }
Dts.TaskResult = (int)ScriptResults.Success;
}

You want the script itself to succeed, unless it encounters an error.

Better yet would be:

public void Main()
{ 
string targetfile = Dts.Variables["PRC_file_path"].Value.ToString();
  try{
 if (File.Exists(targetfile))
            {
                Dts.Variables["file_exists"].Value = true;
            }
            else
            {
                 Dts.Variables["file_exists"].Value = false;
            }

     Dts.TaskResult = (int)ScriptResults.Success;
     }
 catch (Exception Ex)
     {
     Dts.TaskResult = (int)ScriptResults.Failure;
     }
}

Edit:

Forgot to mention that you need to switch the precedence constraints from CONSTRAINT to Expression and CONSTRAINT, where the expression evaluates the @file_exists variable. You should have two success paths, one with the variabled evaluating to true and the other to false.

Upvotes: 1

Related Questions