FTP Task not saving password in designer

I am using CTP 15 and have created an extremely simple FTP task. I enter three pieces of information:

- Server URL
- Username
- Password

Very simple. I click "Test Connection" and it succeeds. If I click "Ok", then try to re-open the connection manager and test again it fails. The password text box is blank, but the properties window in BIDS for the FTP Connection Manager shows "*******" for the password.

This seems like a very basic problem, anyone else have any ideas? My FTP task that utilizes this FTP Connection Manager also fails with a "missing password" type error message.

Thanks,
Josh

This is true of passwords in any connection manager UI... once they're set, the runtime prevents its retrieval so the UI doesn't know if there's a password or not.

regards|||Even though it doesn't appear in the Connection Manager, you should be able to see it in asterisk in the properties sheet for that connection manager.|||

The problem of this is it works fine in Visual Studio execution. But after deploying in server the execution reveals that the password is missing. How can I pack the password in the deployment file.

Thanks,

FTP Task not saving password in designer

I am using CTP 15 and have created an extremely simple FTP task. I enter three pieces of information:

- Server URL
- Username
- Password

Very simple. I click "Test Connection" and it succeeds. If I click "Ok", then try to re-open the connection manager and test again it fails. The password text box is blank, but the properties window in BIDS for the FTP Connection Manager shows "*******" for the password.

This seems like a very basic problem, anyone else have any ideas? My FTP task that utilizes this FTP Connection Manager also fails with a "missing password" type error message.

Thanks,
Josh

This is true of passwords in any connection manager UI... once they're set, the runtime prevents its retrieval so the UI doesn't know if there's a password or not.

regards|||Even though it doesn't appear in the Connection Manager, you should be able to see it in asterisk in the properties sheet for that connection manager.|||

The problem of this is it works fine in Visual Studio execution. But after deploying in server the execution reveals that the password is missing. How can I pack the password in the deployment file.

Thanks,

FTP Task not saving password in designer

I am using CTP 15 and have created an extremely simple FTP task. I enter three pieces of information:

- Server URL
- Username
- Password

Very simple. I click "Test Connection" and it succeeds. If I click "Ok", then try to re-open the connection manager and test again it fails. The password text box is blank, but the properties window in BIDS for the FTP Connection Manager shows "*******" for the password.

This seems like a very basic problem, anyone else have any ideas? My FTP task that utilizes this FTP Connection Manager also fails with a "missing password" type error message.

Thanks,
Josh

This is true of passwords in any connection manager UI... once they're set, the runtime prevents its retrieval so the UI doesn't know if there's a password or not.

regards|||Even though it doesn't appear in the Connection Manager, you should be able to see it in asterisk in the properties sheet for that connection manager.|||

The problem of this is it works fine in Visual Studio execution. But after deploying in server the execution reveals that the password is missing. How can I pack the password in the deployment file.

Thanks,

FTP Connection problem with "The Password was not allowed" error message

Hi

I have a simple FTP task that I am trying to connect to an FTP server. I can test the connection fine, however, when I try and execute the package from Visual Studio 2005 I get the "The Password was not allowed" message. I have found some threads that mention setting the protection level on the package to EncryptSensitiveWithPassword, however, I still get the same message appearing.

Has anyone any idea as to what is causing this and how I can get around it

Thanks

Darrell

The error comes from the FTP server due to a invalid password. Can you tell me what is the value ProtectionLevel property in the Package?

Thanks,

Ovidiu Burlacu

|||EncryptSensitiveWithPassword means you have to enter a password in the package properties. You have specified the wrong password there, most likely.

For running in BIDS, EncryptSensitiveWithUserKey is just fine.|||

I had set the PackageProtection to EncryptSensitiveWithPassword, but that didn't work either.

As for an incorrect password, this can't be correct, because when I test the FTP connection it works fine

Any other thoughts ?

Thanks

D

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

sql

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!
|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!
|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!
|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

sql

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!
|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan

FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!
|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan