SSIS, dynamic expression for variable evaluated incorrectly at execution

Question

Post reply

SSIS, dynamic expression for variable evaluated incorrectly at execution

alicesql

SSCrazy

Points: 2649
More actions
August 1, 2018 at 8:39 am

#374031

Good morning all,
I have a package with a script task which checks for the existence of a flat file. If it exists, delete the flat file, then proceed to a ForEach Loop, otherwise proceed directly to the ForEach loop.
I have 2 variables (package scope) used by the script task: User::Filepath is ReadOnly(String) and User::Result is ReadWrite (Boolean). The script itself is straightforward:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
' The ScriptMain is the entry point class of the script.ImportsImports System SystemImportsImports System.Data System.DataImportsImports System.Math System.MathImportsImports Microsoft.SqlServer.Dts.Runtime Microsoft.SqlServer.Dts.RuntimeImportsImports System.IO System.IO<System.AddIn.AddIn(<System.AddIn.AddIn("ScriptMain""ScriptMain", Version:=, Version:="1.0""1.0", Publisher:=, Publisher:="""", Description:=, Description:="""")> _)> _<System.CLSCompliantAttribute(<System.CLSCompliantAttribute(FalseFalse)> _)> _PartialPartial PublicPublic ClassClass ScriptMain ScriptMainInheritsInherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBaseEnumEnum ScriptResults ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.FailureEndEnd EnumEnum' The execution engine calls this method when the task executes.' The execution engine calls this method when the task executes.' To access the object model, use the Dts property. Connections, variables, events,' To access the object model, use the Dts property. Connections, variables, events,' and logging features are available as members of the Dts property as shown in the following examples.' and logging features are available as members of the Dts property as shown in the following examples.''' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)''' To use the connections collection use something like the following:' To use the connections collection use something like the following:' ConnectionManager cm = Dts.Connections.Add("OLEDB")' ConnectionManager cm = Dts.Connections.Add("OLEDB")' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"''' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.' ' ' To open Help, press F1.' To open Help, press F1.PublicPublic SubSub Main() Main()''' Add your code here' Add your code here '' Dts.Variables( Dts.Variables("Result""Result").Value = File.Exists(Dts.Variables().Value = File.Exists(Dts.Variables("Filepath""Filepath").Value)).Value)
Dts.TaskResult = ScriptResults.Success
Dts.TaskResult = ScriptResults.SuccessEndEnd SubSubEndEnd ClassClass
However, when I execute the package, the File system Task to delete the flat file doesn't execute. Even when it exists. I set a breakpoint and watch the User::Filepath variable. It is not evaluating properly during execution.
The value of the variable in the Variables window is this:
"\\Ahmcfs2\InfoSvcs\NThrive\OutputFiles\1568735660_PLACEMENTS_"+(DT_STR,4,1252) DatePart("yyyy",getdate()) +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)+ ".txt"
I have also set an expression for the variable in Expression Builder where all the backslashes are doubled. This evaluates properly in the expression builder. (screenshot attached showing expression and presence of flat file in directory specified)
But when the package executes, the value of the variable is incorrect, extra backslashes are in the path.:
The User::Result variable is False during execution.
The precedence constraint for the left most precedence is as seen here:
and the other precedence constraint is like this:
I'm a bit confused at this point, any help would be appreciated, Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

I believe that you have over-complicated things. There is no need to split the logic into two paths.

Have a single path to your script task. The script task deletes the file if it exists, otherwise it does nothing.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246991 More actions · Answer 2

Can you also check the text of the script which you have posted? It seems to be all mixed up and will never run as it stands.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

alicesql SSCrazy Points: 2649 More actions · Answer 3

Thanks for responding Phil. I think the editor mixed things up quite a bit. It does run, and looks much better when I 'Edit Script'
I will change the script to delete the file if it exists, then I wouldn't need the conditional precedence constraints, etc.

alicesql SSCrazy Points: 2649 More actions · Answer 4

For reference:

I ended up simply using a File System task to delete the output file. I was concerned that the task would fail since normally the file would not exist. I found if I changed the precedence constraint to 'Completion', it wouldn't matter whether the file existed or not (see the blue line?). The other issue I was having was the expression for the variable was not evaluating properly during execution. I then deleted the connection manager and created a new one using the same expression, and it worked fine.