SQL Server-Based Configurations

  • I have an SSIS package that, in one step, hits an FTP site to download files.  Works great during dev.  However, the site host, username, and password are different on the test site and the prod site.

    So I set up a couple configurations, one for test and one for prod, which included the FTP connection manager server, username, and password.  Because this included a password, I do not wish to store the config as an XML file, so I followed the BOL advice and create these configurations to be stored in SQL Server.

    So I deploy the package to another server, modify the table values for one of the configuration filters, and go to run the package.

    Here's the question: How do I tell the runtime to use one of my SQL Server-based configurations?  The only configuration choices I see available are to load one of the config XML files.

    Notes:

    -   I tried fiddling with the manual connection manager setting for the FTP site connection manager in the run dialog to use the username:password@ftp.site.com syntax, but it threw up with 'cannot resolve server'.

    -  The only other option I see is to dig up the full property path for each item (in this case, "\Package.Connections[FTP Connection Manager].Properties[ServerPassword]") and set it from the 'Set Values' tab.

    -  I also tried just storing the value in a table and pulling it into a variable in the first step of the package.  That works great for most properties (server name, user name), but the password is not an exposed property to which an expression can be assigned.

    Has anyone managed to select SQL Server based configurations when running or scheduling a package?

    Eddie Wuerch
    MCM: SQL

  • I initially went down a dev path where I thought I was going to use Sql Server based configurations but I quickly found out that there is little documentation.  Makes me think they added it late in the dev cycle.  Anyway, check out Kirk's blog and search around....I remember him writing about this.  Sorry I don't have the time to look it up myself.

     

    http://sqljunkies.com/WebLog/knight%5Freign/

Viewing 2 posts - 1 through 1 (of 1 total)

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