ability to use variables in package configurations and set their values at runtime

Hi,

My scenario:

I am using a FTP Connection Manager and the configuration setting for it is being set in the package configuration xml file. So the xml file contains the Ftpserver, FTp server username and password. The package is picking up the values from the xml file and is executing successfully. I have to do this because I was not able to provide an expression to the Connection Manager Server Password property.

Now, I want to pick up the ftp details from a database table and set it in the xml file during runtime. Is this possible? OR something like using the

<Configuration ConfiguredType="Property" Path="\Package.Connections[FTP Connection Manager].Properties[ServerPassword]" ValueType="Variable"><ConfiguredValue>@.[user::FtpPassword]</ConfiguredValue></Configuration>

Kindly look at the items in bold. Is this possible? Then I can set the value of the variable in the package before the FTP connection manager task is executed.

Thanks for all the help.

$wapnil

You could use a configuration storage as DB.

Or you can use Script task to query the DB and set the FTP connection properties or Variable values at runtime

Or even Execute SQL task to get the variable values from DB|||

Thanks

Reply to the options that you have provided

1) We want to use XML for the configuration. Just something which we want to follow if we could.

2) We are trying to minimuize the script tasks in the package by bringing the configuration details outside of the package and storing it in XML.

3) same as 1

Lets take a FTP connection Manager for example. The details of the FTP server are present in the database and I have also created variables for that but atleast I am not able to set the FTP Server password using a variable so I resorted to using the xml file and putting in the password there.

Now if I want to connect to multiple FTP server using the same package and the connection details are there in the database then I can pull out the connection details using an Execute SQL task but then how to update the xml files with values......knowing that the FTP server password can be only be set dynamically using the xml file.

Correct me if I am wrong. is storing the variable in the DB the only solution.

Thanks,

$wapnil

|||

spattewar wrote:

Now if I want to connect to multiple FTP server using the same package and the connection details

Are you trying to connect multiple FTP servers at the same time or are you trying to run the package in different environments?

For example if I would need to pull info from 3 different FTP servers - I would create 3 different configuration files:
1 for Development
1 for staging
and 1 for Production site|||

I am trying to connect to multiple FTP servers. But it can be at the same time or in sequence. But I am planning to use a single package for this task.

Thanks for your response.

$wapnil

|||Why don't you use Parent Package Configurations instead? A simple 2-package setup could work where:
Your mother package cycles through a list of FTP server configurations or whatever is needed.|||

That looks like a good idea.

But there is one concern. Even though I pass the configuration as a parent package variable to the child package, will I be able to set the FTP ServerPassword property of the FTP connection manager using a variable. I doubt I can do that, then it boils down to the same thing picking up the package configuration from the xml file or a database, here we would prefer xml file.

Maybe we can do this by.

1) Running the master package which will have a script task to update a xml configuration file.

2) Pass that xml file as a configuration parameter to the child package and execute the child package.

Thanks for your response.

$wapnil

|||I don't have any reason to believe that you can't pass the password or even the whole connectionstring as a variable.

However do note that if your variable values refuse to change (although I don't see any reason why they won't) you might have to tweak the ProtectionLevel of the package to DontSaveSensitive or some other value that allows you to modify your connectionstring variable.

HTHsql