Get Value for Global SSIS Variable in Script Task

[ilin]

In DTS I used workflow ActiveX script to determine File Existence and depends on the result run one of the following tasks.

I had a global variable with FileName. Then in ActiveX script I had script like this:

Function Main()
Dim oFSO, sFileName

sFilename = DTSGlobalVariables("FileName").Value

Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If

Set oFSO = Nothing

End Function

Moving to 2005 SSIS I need to create ScriptTask instead.

I created Precedence Constraint depends on value of FileFound variable. FileName and FileFound are global for SSIS variables.
My script looks like:

Dim FileFound As String
Dim sFileName As String
sFileName = Dts.Variables.Item ("FileName").Value.ToString()
If File.Exists(sFileName) Then
Dts.Variables("FileFound").Value = "1"
MsgBox(FileFound)
Else
Dts.Variables("FileFound").Value = "0"
MsgBox(FileFound)
End If
MsgBox(FileFound)
Dts.TaskResult = Dts.Results.Success

Executing this task I got error: when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

Why can not I get sFileName from Global Variables the way I am doing?
Please help.

 

[MDXer]

I don't use the ActiveX script task, but when using the VB Script task you have to declare your read and write variables which are also Case Sensitive at all levels.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ilin]

Paul,
Thanks very much, it is so simple and it works!

 

[ilin]

How can I assign value to Global variable in SSIS using ScriptTask.

The statement Dts.Variables("FileFound").Value = "1"
gives me an error.

Any advice? Should I lock variable before assigning value and if yes what would be the statement?

 

[MDXer]

if FileFound is a bollean you should use

dts.Variables("FileFound").Value = TRUE

other than that it appears correct.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ilin]

I had it as string, but it still does not work even I changed to boolean. Should I lock the global variable before I assign value to it?

 

[MDXer]

not sure about ActiveX scripts but I don't have to lock them in the VB Script task as it locks it already. is your FileFound a ReadWrite Variable?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

I just looked at the ActiveX script transform object and it is different than the Script Task which uses VB.NET so I would say that you probably do have to lock the variable.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ilin]

Yes, this is a ReadWrite Variable.
So I guess I need to lock it, right?

 

[MDXer]

We have come to an end of my knowledge on the ActiveX script task you may want to look at moving your Script tasks to the standard Script Task object. THe ActiveX script task is due to be removed in the next version of SQL.


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ilin]

Sorry Paul for the confusion and thanks for your help.
But even in my first message I stated that I am trying to migrate ActiveX Script in 2000 to ScriptTask in SSIS 2005.
I created Package Scope variables FileName and FileFound as String.
In ReadWriteVariables I put FileFound and in expression I put ReadWriteVariables @[User::FileFound]

In ScriptTask I created the following script:

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.VariableDispenser

Public Class ScriptMain
Public Sub Main()

Dim sFileName As String
Dim sFileFound As String

sFileName = Dts.Variables.Item ("FileName").Value.ToString()


If File.Exists(sFileName) Then
sFileFound = "1"
'MsgBox(sFileName)
MsgBox(sFileFound)

'Dts.VariableDispenser.LockForWrite("FileFound")
Dts.Variables("FileFound").Value = "1"
MsgBox(Dts.Variables("FileFound").Value)


Else
sFileFound = "0"
Dts.Variables("FileFound").Value = "0"
MsgBox(Dts.Variables("FileFound").Value)

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

File was found and I got "1" for local sFileFound variable.

Executing this task I got error: when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

Please notice, that I got back FileName and local sFileFound.

Can you please advice?

 

[MDXer]

The error message is indicating that a variable is being used but not passed. You have the FileFound Variable as FileFound in the Package with that exact Syntax and with a scope visible by the Script Task correct? Then double check to make sure you have FileFound as a ReadWriteVariable and not a ReadVariable. Make sure you have no spaces before or after the variable name in the script task.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

I was able to build a test package with the 2 variables in your script. I then added a ScriptTask and set the RedVariable to FileName and ReadWriteVariable to FileFound. In the script code I pasted your script AS IS and was able to execute the Script without error.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ilin]

Thanks Paul,
I have the same. I have FileName as ReadOnlyVariables and FileFound As ReadWriteVariables.

Also in Script Task Expressions I have @[User::FileFound]

On a Package Level I have Variable FileFound on User NameSpace without any value in it, declared as String.

Code is exactly as I put above.
And I still get this error.

Any idea what could cause it?

Thanks a lot,
Ilin

 

[MDXer]

What are the scope values for the 2 variables?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ilin]

I found the problem. I had evaluate as expresion property set as true. After I fixed it it is correct

 

[ilin]

Thanks Paul very much for your time and help

 

[MDXer]

glad you got it working.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck