1

In a SSIS package, I have a simple script checking if the file exists. If it doesn't then the process flow should stop.

The script is correctly returning the file exist statement is false

Message

And based on the current precedence constraint, it should proceed only if the script returns value as true

Constraint

Here is the File exist flag for reference

enter image description here

However, the package continues and then fails. I do not understand what I missing here or what I set up incorrectly.

EDIT: Adding the actual script 

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

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception Ex)
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
        MessageBox.Show("File.Exists(targetfile): " + File.Exists(targetfile));

    }
EzLo
  • 13,780
  • 10
  • 33
  • 38
Hristo
  • 11
  • 3
  • Make sure that your script is actually assigning the value to the variable, and your variable is set as **Read/Write** in the Script's configuration menu. – EzLo Nov 12 '19 at 12:27
  • The variable is readwrite in the menu. I added the script. It seems be to working fine. – Hristo Nov 12 '19 at 12:52
  • Do you by any chance have the `FileExists` variable marked to be calculated as an expression? It shouldn't if it is. – EzLo Nov 12 '19 at 12:54
  • "Evaluate as expression" is currently set at False. – Hristo Nov 12 '19 at 13:06
  • 1
    Everything seems OK and it should be working fine... try adding a breakpoint on the Post-Execute phase of the script and the Pre-Execute phase of the data flow that is trying to open the file, and check the actual running value of the variable using the watch window. – EzLo Nov 12 '19 at 13:22
  • By "fails" do you mean it goes to the data flow task and fails on that because the file doesn't exist? Out of interest why don't you remove the `==True` altogether from your expression and see what happens. Also reverse the expression in your precedence constraint (`==False`) and see if it does what you expect. – Nick.Mc Nov 12 '19 at 13:31
  • Thank you, guys. @EzLo can you share some guidance how I can create these breakpoints as I am still new in SSIS. – Hristo Nov 12 '19 at 13:44
  • @Nick.McDermaid Correct. It goes to the data flow task, does not find the file and fails. I tried both ideas you suggested but the result is the same as described above. It looks like the script always returns true which is just leaving me speechless. – Hristo Nov 12 '19 at 13:44
  • 1
    So if you make the constriant `== false`, the DFT doesn't run? What happens if you hard code the script to return false? – Nick.Mc Nov 12 '19 at 13:48
  • Also, be _very_ wary of scope. For simplicity I always ensure all of my variables are at the package scope. Otherwise you'll reference the wrong variable (with the same name) without realising it. – Nick.Mc Nov 12 '19 at 13:51
  • The package scope of the variable is fine. Interesting part is that even if I hardcode the variable (put both cases of Dts.Variables["User::FileExists"].Value to false), the package is still trying to execute the data flow task. – Hristo Nov 12 '19 at 14:14
  • In your package variables, what data type do you have assigned to `FileExists`? Boolean, or something else? – Eric Brandt Nov 12 '19 at 19:31
  • What happens if you set the variables default value to False? – Nick.Mc Nov 12 '19 at 21:14
  • @EricBrandt, FileExists is a Boolean and the default value is False. – Hristo Nov 13 '19 at 08:16
  • @Nick.McDermaid I shared a screenshot above and the default value is False. If I move it to True and run the package, it stops without any error message. – Hristo Nov 13 '19 at 08:17
  • Update - I managed to find a solution. The error was appearing before even the script start running and checking the file. It came out that it was due to the setting on the connection manager in the cases when the file is missing. Here is the solution: https://stackoverflow.com/questions/10293401/how-to-prevent-excel-import-in-ssis-package-when-there-is-no-file-to-process – Hristo Nov 14 '19 at 15:26

0 Answers0