SSIS script task error returning variable values

Question

In Script Task , I am trying to extract a file from a network location and FTP this file to a remote location

• In SSIS, I created FTP Connection and verified that it was configured and working.

• Three variables created

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

• Created a Script Task and added 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 item could not be found in the collection. This error occurs when you try to retrieve an item from a collection in a container during package execution, and this item is missing.

So, I commented and found that the error is generated when retrieving the value of a variable, but I do not know what is wrong here.

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

I tried several search options and all get the same error. Does anyone see something wrong?

Answer 1

Two things:

• Make sure that you configure the Script task to read access to the variable. To do this, right-click the Script task and select Modify. Click the ... button in the ReadOnlyVariables section.
• Fully qualify your variables, such as Dts.Variables["User::RemotePath"].Value