JeffK627 JeffK627 - 23 days ago 9
Vb.net Question

SSIS Scripting Task Can't Find Variable

I have an SSIS package with a scripting task to set a file's name and location. I recently tried to update it to add an incrementing number to the end of the file name. When I run it, I get an error message:

Error: 0xC0014054 at Script Task: Failed to lock variable "System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.


There's a lot more after that, but this is the gist.

The thing is, it finds all the variables earlier in the same code, and since it doesn't say which variable it can't find, I don't know what the problem is. I was able to track down the line it's failing on though Here's my code:

Public Sub Main()
Dim sFile As String
Dim i As Integer
Dim bExists As Boolean

i = 1
bExists = True

Dts.Variables("User::UStartTime").Value = Dts.Variables("System::StartTime").Value
Dts.Variables("User::FileName").Value = Dts.Variables("User::Protocol").Value.ToString _
& "_" & CStr(Format(Dts.Variables("User::UStartTime").Value, "yyyyMMdd")) _
& "_" & CStr(i)

Dts.Variables("User::FileLocation").Value = "\\ACMSHARES2\clntrial\DataMgt\" _
& Dts.Variables("User::StudyNumber").Value.ToString _
& "\" + Dts.Variables("User::FileLocation").Value.ToString _
& Dts.Variables("User::FileName").Value.ToString _
& "." & Dts.Variables("User::FileType").Value.ToString

'Add incrementing number to end of file name per DMA
Do Until bExists = False
'SCRIPTING TASK FAILS ON THE NEXT LINE
sFile = Dts.Variables("User::Filelocation").Value

If File.Exists(sFile) Then
i = i + 1
Dts.Variables("User::FileName").Value = Dts.Variables("User::Protocol").Value.ToString _
& "_" & CStr(Format(Dts.Variables("User::UStartTime").Value, "yyyyMMdd")) _
& "_" & CStr(i)
Dts.Variables("User::FileLocation").Value = "\\ACMSHARES2\clntrial\DataMgt\" _
& Dts.Variables("User::StudyNumber").Value.ToString _
& "\" + Dts.Variables("User::FileLocation").Value.ToString _
& Dts.Variables("User::FileName").Value.ToString _
& "." & Dts.Variables("User::FileType").Value.ToString
Else
bExists = False
End If
MsgBox("Loop iterated")
Loop

Dts.TaskResult = ScriptResults.Success
End Sub


So it fails when it reaches the line
sFile = Dts.Variables("User::Filelocation").Value


Any ideas?

Answer

While VB is case insensitive, SSIS Variables are case sensitive. You have used a lowercase L in FileLocation

sFile = Dts.Variables("User::FileLocation").Value
Comments