Change FTP Connection Properties via Script Task

Question

Post reply

Change FTP Connection Properties via Script Task

dbaforever

Hall of Fame

Points: 3740
More actions
March 20, 2009 at 4:03 pm

#129971

hi experts,
As I understand, you cannot store the FTP password in the FTP Connection Manager - so I need a way of assigning that value to be able to run the package as an Agent job.
All the examples I have seen are ADDING a connection dynamically. I already have my Connection Manager created for the FTP site.
My simple question is how do you *modify* the password (or any other property) within the script code?
Thanks, obviously I'm a bit lost.
John
This is the Script Task, at the top of my package:
Public Class ScriptMain
' This script will assign values for the FTP site password
' If this script task is removed, the package can be ran on-demand via IDE but will fail as an Agent job because password cannot be stored in the connection manager
Public Sub Main()
'Create the connection to the ftp server
Dim india As ConnectionManager = Dts.Connections.Add("FTPIndia2")
'Set the properties such as username & password
india.Properties("ServerName").SetValue(india, "999.999.999.999")
india.Properties("ServerUserName").SetValue(india, "pnclftpuser")
india.Properties("ServerPassword").SetValue(india, "passpncl12*")
india.Properties("ServerPort").SetValue(india, "21")
india.Properties("Timeout").SetValue(india, "0") 'The 0 setting will make it not timeout
india.Properties("ChunkSize").SetValue(india, "4") ' number of KB
india.Properties("Retries").SetValue(india, "2")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

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

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

dbaforever Hall of Fame Points: 3740 More actions · Answer 1

dbaforever

Hall of Fame

Points: 3740

March 23, 2009 at 8:27 am

#964449

Anyone?

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

John Bates (3/20/2009)
hi experts,
As I understand, you cannot store the FTP password in the FTP Connection Manager - so I need a way of assigning that value to be able to run the package as an Agent job.
All the examples I have seen are ADDING a connection dynamically. I already have my Connection Manager created for the FTP site.
My simple question is how do you *modify* the password (or any other property) within the script code?
Thanks, obviously I'm a bit lost.
John
This is the Script Task, at the top of my package:
Public Class ScriptMain
' This script will assign values for the FTP site password
' If this script task is removed, the package can be ran on-demand via IDE but will fail as an Agent job because password cannot be stored in the connection manager
Public Sub Main()
'Create the connection to the ftp server
Dim india As ConnectionManager = Dts.Connections.Add("FTPIndia2")
'Set the properties such as username & password
india.Properties("ServerName").SetValue(india, "999.999.999.999")
india.Properties("ServerUserName").SetValue(india, "pnclftpuser")
india.Properties("ServerPassword").SetValue(india, "passpncl12*")
india.Properties("ServerPort").SetValue(india, "21")
india.Properties("Timeout").SetValue(india, "0") 'The 0 setting will make it not timeout
india.Properties("ChunkSize").SetValue(india, "4") ' number of KB
india.Properties("Retries").SetValue(india, "2")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

John,

Of course you can store your password in the FTP Connection Manager. You have to make sure you use appropriate package protection level and execution options in the SQL Job Agent. Different execution ways are described here.

Now you can also specify the password using script task as well. This is done by setting up the password in the ConnectionString property.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

dbaforever Hall of Fame Points: 3740 More actions · Answer 3

dbaforever

Hall of Fame

Points: 3740

March 23, 2009 at 1:56 pm

#964745

Now that looks promising.

Thanks CozyRoc.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 4

Here[/url]'s the script to set the password programatically.

Kindest Regards,

Frank Bazan