SSIS script task error retrieving variable values

Question

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?

Answer 1

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