Why should i have to protect my package with a password in Visual Studio in order to run my job in Management Studio properly? I thought that the setting "EncyrptSensitivewithPassword" just refers to the package or do i have to protect my packages with passwords in order to execute them on the management Studio?

I have the same problem.

I can run ftp.exe and do my ftp task. but it just doesn't work when i do it through ftp task.

I'm connecting to a unix box and my path is in the /dir1/dir2/dir3/file.trg format.

i'm using a variable to store this value.

what is wrong? Anyone?

Hello ,
             I wanted to know how to make a dynamic FTP Connection. My FTP Server will be having a FTP server Name , File path, FTP Server Name, FTP User Name, FTP Password. Our Database maintains different FTP servers for different clients.In DTS we used below code in activex script task to connect to the FTP Dynamically.

"Set objTask = objPackage.Tasks(cstr("DTSTask_DTSFTPTask_2")).CustomTask
 
 objTask.SourceLocation = 0
objTask.sourcesite = DTSGlobalVariables("g_str_ftpservername").Value
 objTask.SourceUsername = DTSGlobalVariables("g_str_ftpusername").Value
 objTask.SourcePassword = DTSGlobalVariables("g_str_ftppassword").Value
objTask.NonOverwritable =  False
objTask.SourceFilename =  srcFiles
objTask.destsite = DTSGlobalVariables("g_str_filelocation").Value
 
                Now we are migrating our workflows to SSIS (SQL Server 2005). The problem is we are unable to find solution for dynamically connecting to FTP Server which maintains a password.
 Can any body help please.
Thanks,
Kiran G

Hi,

I haven't found a solution, but I have found a working around using the Execute Process Task.  In the Execute Process Task Editor, Process page I set:

• Executable to ftp.exe
• Arguments to -s:FTP_Script.txt
• Working directory to C:\

The C:\ directory contains my FTP_Script.txt file, which logons to the remote FTP server and transfer my file named TRGL.TXT from the FTP server root directory to the c:\ drive of the SQL server:

OPEN FTP_SERVER
logonid
password

GET /TRGL.TXT C:\trgl.txt
DELETE /TRGL.TXT

bye

It annoys the crap out of me, because it ignores the apparent functionality of the FTP Task, but what else can a bloke do?

I had the same problem with my FTP Task.  The problem is that the package views the FTP password as "sensitive information" and the default setting is "EncryptSensitiveWithUserKey".  This setting will only allow the user name that created the package to run it successfully.

When you create the job step to execute the package, the package is not running under your user key.

The workaround that I was able to implement was to change the encryption to "EncryptAllWithPassword".  Right above that property, change the PackagePassword by clicking on "..." and inputting a password.  Now, whenever you open the package, you will need to have a password in order to edit it.  To ensure that this functions properly, close the package after changing the setting, re-open it to edit and it should prompt you for the password.  Enter in the password for the FTP connection and then re-save it and close.

Within the job step, when you try to edit the command line, it should prompt you for the package password.  Once you enter the package password, it should add the following switch to the command line "/DECRYPT <password>", where <password> is your package password.  The only issue here then is that the command line is not encrypted, but I satisfied for now to have the package working properly as a job.

I was able to successfully execute my package, which includes an FTP upload, with no problems from the SQL Server Agent account by using this methodology.

 Public Sub Main()
       'TODO: assign variables here...
           dim password as string = dts.variables("vPassword").value.tostring 'the decrypted password

           'Get instance of the connection manager.
            Dim cm As ConnectionManager = Dts.Connections("FTPConnMgr")
            'Set the password property to the decrypted password
            cm.Properties("ServerPassword").SetValue(cm, password)

            '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()

             ftp.SetWorkingDirectory(remoteDir) 'set the remote directory
 
            Dim files(0) As String
            files(0) = fileToGet 'eg. File1.trg

            'Get the file
            ftp.ReceiveFiles(files, localDir, 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

    End Sub