SSIS package using "Script Task" package stops with completion precedence constraint

Question

SSIS package using "Script Task" package stops with completion precedence constraint

Doug Elwood-471888

SSC Eights!

Points: 953
More actions
June 22, 2017 at 9:07 am

#337691

I was adding some logic to an existing SSIS package to determine if the Excel import file had the correct column name. To do this, I used a "Script Task" to compare the contents of two variables. (VB script)
The flow has two possible outcomes: (constraint editor listed below for both conditions)
If the flag variable = 1 continue as normal
If the flag variable = 0 send email and stop
I used a MsgBox to show the value was getting set as expected, but the package just stops executing regardless of the value of my flag variable.
For a bit of background - I have been using SQL over 20 years, using DTS/SSIS off and on for a long time.
The package cannot be ran in the debugger - issues with Office 64 bit versus 32 bit drivers are causing issues - even with the debug setting Run64BitRuntime set to false.
I have to deploy the package to our development server to test - so I cannot watch it execute. A screen shot of the Script Task and the two other steps it should be executing are below.

The Precedence Constraint Editor settings for both paths from the Script Task: (the left side)

And now the right side:
Excerpt from the script:
Public Sub Main()
   '
   ' Add your code here
   '
   Dim DefaultColumnNamesStr As String
   Dim ExcelColumnNamesStr As String
   DefaultColumnNamesStr = Dts.Variables("User::DefHeader").Value
   ExcelColumnNamesStr = Dts.Variables("User::FileHeader").Value
   If DefaultColumnNamesStr <> ExcelColumnNamesStr Then
    Dts.Variables("User::ValidationFlag").Value = 0
    'MsgBox("Columns do not match")
   Else
    Dts.Variables("User::ValidationFlag").Value = 1
    'MsgBox("Columns match")
   End If
   Dts.TaskResult = ScriptResults.Success
End Sub
The MsgBox (when not commented out) behaved as expected. The package execution just stopped after the script finished. (with or without the MsgBox prompts)
It is almost like SSIS decided to run my script in a new process thread and when it finished, so did SSIS. Execution shows validation of steps beyond the script, but I am a bit puzzled why it does not continue.
Help or suggestions are welcome, I can skip the Script Task if there is a better way to do this. We just do not want Excel import file mistakes (bad data, bad columns, etc.) to cause the process to fail without alerting someone.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

A couple of thoughts:
1.) Data Type for the package variable @[User::ValidationFlag] - Boolean or Int32 ?
You might need to make that Boolean for best results, but just wanted to check that if Boolean, you don't want to be comparing it to 1 and 0. Compare it to True and False instead.

2.) What does your Progress tab show when execution ends? I'm referring to when you run it locally.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Doug Elwood-471888 SSC Eights! Points: 953 More actions · Answer 2

Steve, the package variable [User::ValidationFlag] is Int32 - which seems to not be too much of a problem. What I have been told is Visual Studio 2015 and SQL Server 2012 with scripting might be problematic. I will upgrade the SSDT next week to see if that has any impact. I have hit the "Edit Script" button and had the VS2015 application hang a few times.

Since I cannot run this package in the debugger, I just run it from the SSIS catalog using "Execute" - I have no progress tab... When I had the MsgBox statements uncommented, they worked as expected, but it was like the script never returned control to the next step in the SSIS package.

I am willing to try running this from the command line or in an incarnation that would provide more insights - right now I want to try the newer SSDT to see if it resolves my problem.

Thanks!