I have an SSIS package that has a script task that compares the date from a database table column and the date of a file on a network share. I'm using the following VB script in the script task. The "maxEvaluationDate" is set from a sql task the returns the max date from a database table and saved to a variable.

Public Sub Main()
    Dim maxEvaluationDate As DateTime = CType(Dts.Variables("User::actual_excel_data_DB_table_MaxEvaluationDate").Value, DateTime)
    Dim fileDate As DateTime = File.GetLastWriteTime("\\servername1\folder1\folder2\Import\actual_excel_data_file.xlsx")

    Dts.Variables("User::actual_excel_data_file_FileDate").Value = fileDate

    If fileDate > maxEvaluationDate Then
        Dts.TaskResult = ScriptResults.Success
    Else
        Dts.TaskResult = ScriptResults.Failure
    End If

End Sub

If the check fails, that means that the file isn't newer than the date from the DB table column and that's OK but the script task will return fail indicating that the package failed, and that's not the case. If the date check fails, I just want the package to stop executing and return that is completed successfully.

I have an SSIS package that has a script task that compares the date from a database table column and the date of a file on a network share. I'm using the following VB script in the script task. The "maxEvaluationDate" is set from a sql task the returns the max date from a database table and saved to a variable.

Public Sub Main()
    Dim maxEvaluationDate As DateTime = CType(Dts.Variables("User::actual_excel_data_DB_table_MaxEvaluationDate").Value, DateTime)
    Dim fileDate As DateTime = File.GetLastWriteTime("\\servername1\folder1\folder2\Import\actual_excel_data_file.xlsx")

    Dts.Variables("User::actual_excel_data_file_FileDate").Value = fileDate

    If fileDate > maxEvaluationDate Then
        Dts.TaskResult = ScriptResults.Success
    Else
        Dts.TaskResult = ScriptResults.Failure
    End If

End Sub

If the check fails, that means that the file isn't newer than the date from the DB table column and that's OK but the script task will return fail indicating that the package failed, and that's not the case. If the date check fails, I just want the package to stop executing and return that is completed successfully.

• vba
• ssis
• script-task

• 1 You can have the script return a value into a variable (pass/fail) then in your SSIS flow you can do a precedence constraint check (check the value of the variable returned if pass/fail to determine if you should continue with the rest of the package execution). You can do that by double clicking on the green line in your process flow and can set an expression to continue executing or not. – Brad Commented Mar 10 at 19:27

1 Answer 1

More details on my commment:

In your script task instead of setting success/fail you can set a variable to be returned from the script task.

You can set it to a date, or just pass/fail true/false. This variable can then be used in your SSIS flow you can do a precedence constraint check (check the value of the variable returned if pass/fail to determine if you should continue with the rest of the package execution).

You can do that by double clicking on the green line in your process flow and can set an expression to continue executing or not.

So if you click the green arrow in your SSIS flow it will pop up with a box like this (my example has an expression already filled in):

In the expression you evaluate whatever the value you returned is to determine if it should continue or not. After you add the expression the green lines will have the fx in it.

The below screen shot has 2 checks, one if value is XX then it goes to the left flow, if it is YY then it goes to the right path.

You do not need to have 2 paths. If you just set it to continue if it has a date in your process (for your example, or whatever value you set for success/failure). Then if it does not meet the criteria to continue it will just stop processing.

Sometimes though you want something else to happen if you do not continue processing, an email for notification, or something.