CODEWITHSUNDEEP
vb.netssissql-server-2008-r2
JeffK627
JeffK627

Reputation: 305

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?

Upvotes: 1

Views: 1522

Answers (1)

billinkc
billinkc

Reputation: 61259

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

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

Upvotes: 3

Related Questions