SSIS script task error retrieving variable values

6
votes

In a Script Task, I am trying to retrieve a file from a networked location and FTP that file to an offsite location

  • In SSIS I created the FTP Connection and tested that it is setup and works

  • Created three variables

    • variable 1. FullPathName = \ftpservercsc\\\Filename.txt
    • variable 2 FTPFilePath = \ftpservercsc\\\
    • variable 3 FTPFileName = Filename.txt

  • Created a Script Task and added the vb code as such ...

    'Get instance of the connection manager.
Dim cm As ConnectionManager = Dts.Connections("FTP Connection Manager")
Dim remotePath As String = Dts.Variables("FTPFilePath").Value.ToString

'create the FTP object that sends the files and pass it the connection
'created above.
Dim ftp As FtpClientConnection = New FtpClientConnection
                                        (cm.AcquireConnection(Nothing))

'Connect to the ftp server
ftp.Connect()

'Set the path on the FTP server where dropping files
'ftp.SetWorkingDirectory("/Prequalify") 'set the remote directory

Dim files(0) As String
files(0) = Dts.Variables("FTPFileName").Value.ToString 'eg. File1.trg

'Send File
ftp.SendFiles(files, remotePath, True, True)

' Close the ftp connection
ftp.Close()


'Dts.Events.FireInformation(0, context, "File " + fileToGet 
'     + " retrieved successfully.", Nothing, Nothing, True)
Dts.TaskResult = Dts.Results.Success

  • Error: 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.

So I have commented out and found the error is generating on retrieving the variable value but I do not know what is incorrect here 

    Dim remotePath As String = Dts.Variables("FTPFilePath").Value.ToString

I have tried multiple variable retrievals and all get the same error. Anyone see anything wrong?

1
ssis

1 Answers

16
votes

Two things:

  1. Make sure you config the Script Task to have Read Access to the variable. To do this right-click on the Script Task and select Edit. Click the ... under ReadOnlyVariables.
  2. Fully qualify your variables such as Dts.Variables["User::RemotePath"].Value