Use New Folder and FTP

Question

Use New Folder and FTP

Tim Riley-492074

SSCommitted

Points: 1549
More actions
March 5, 2008 at 1:23 pm

#120684

I have a Script that creates a folder, (the folder name is the current date), it works fine. I need to copy files from a folder on an FTP site with the same name. My problem is how do I use/assign the value of the new, and FTP, folder to the FTP task? I haven't written VB code in 5 years and this is my first venture into scripting. TIA
Tim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

First step is to add a package variable. Then in the Script Task add the new variable name to the ReadWriteVariables property of the task. The variable is then set in the script.

DTS.Variables("myVar").value = "/remoteFTPFolder/myFile.txt"

or

DTS.Variables("myVar").value = "/remoteFTPFolder/*.*"

To use that variable in the FTP Task set IsRemotePathVariable to True and select your variable.

Change the operation to Receive Files and specify the path or set that to a variable too.

Tim Riley-492074 SSCommitted Points: 1549 More actions · Answer 2

Okay, I got the folder create to work, I didn't know things were case sensitive. However, now I can't pass the variable to an FTP task. What the heck am I doing wrong?:crying:

The error I get during validation is "Can't lock variable for read access...the variable cannot be found". The variable is the same one used to create the folder.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

Check that the scope of the variable 'archivefolder' is set to the package. You'll probably have to modify the variable or have an additional one that can be used with the FTP Task because the drive letter in the path would probably fail in the FTP Task.

Tim Riley-492074 SSCommitted Points: 1549 More actions · Answer 4

I tried that and got this message when I executed the task:

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.

This is the script; can you tell me I'm doing wrong? The variable "archivefolder" is set as a ReadWrite variable.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

'

Dim Archive As String

Dim DateString As String

Archive = "c:\Test Files\"

DateString = Right("0000" & DatePart(DateInterval.Year, Now), 4)

DateString = DateString & Right("00" & DatePart(DateInterval.Month, Now), 2)

DateString = DateString & Right("00" & DatePart(DateInterval.Day, Now), 2)

Archive = Archive & DateString

MkDir(Archive)

Dts.Variables("archivefolder").Value = Archive

'

Dts.TaskResult = Dts.Results.Success

End Sub

End Class