Simple Steps to Creating SSIS Package Configuration File

  • Comments posted to this topic are about the item Simple Steps to Creating SSIS Package Configuration File

  • What do you do with the configuration file after you have it created and modified?

  • I have found it much easier to maintain connection strings if you store them in a SQL configuration table instead of XML. You can then use simple scripts to do mass updates for multiple configuration packages when moving between environments.

    Aigle de Guerre!

  • I have an odd problem with package config files. There really aren't many steps involved, so I'm not sure where I could be making a mistake.

    The experience I've had is that sometimes the config settings seem to be applied and at other times they don't.

    I have tested with a single variable and XML config file, setting JUST the value of the variable (as well as trying the complete settings for the variable). At run time the log says the config was read, but the value seems to be the default value.

    Any ideas what I could be missing?

    Thanks,

    jeffa

  • jeberhard (5/14/2009)


    What do you do with the configuration file after you have it created and modified?

    After you have created/modified a configuration package, your SSIS package will use a defined order to determine property values during execution. The config file values will be used over what you defined in the package, parent package values will supercede the config file values if defined. The SSIS package will use the config file in the location where you define it.

    Aigle de Guerre!

  • We've recently started using package configurations in our Development/Test/Production environment since developers cannot access test and production.

    We used the process outlined by Ray Barley http://www.mssqltips.com/tip.asp?tip=1405 that uses an Environment variable to point to an SSIS Configuration database.

    I'm sure there is a benefit to using XML configuration files, but at this time we chose the database method instead for storing our configurations.

  • I haven't used environmental variables because they are local to the system you are running the package on. If you create a variable during development, you need to make sure that same variable exists on all systems where published.

    Aigle de Guerre!

  • Famson, thanks for the article. But, I hate using XML for configuration when you can store same in a configuration table.

    SQL DBA.

  • Storing your configuration file as XML is only one of the options available. Your choice will depend on your work environment and what fits into it.

    Using XML is just a way of making the configuration settings easy to modify and independent of any tool. This is where portability comes into your package deployment process, making it very easy to move around and deploy on any server.

    Thanks for your comments and keep posting them.

  • We use XML package configuration files in our development and production environments, primarily to store connection info and relative path info. We store our packages in the file system, rather than in SQL Server, and execute them from the SQL Server Agent accordingly. We use environmental variables on each server to store the location of the package config files. While it seems a bit cumbersome at first, once it is set up moving packages from development to production as simple as drag-n-drop. If a connection parameter needs to be changed, it's a simple matter of editing the XML-based text file instead of modifying the value in the DB. Some would challenge the security considerations of this approach, but in our small shop it works well.

  • I'm currently using a SQL server table to store the SSIS configuration settings for my packages. I haven't played with the XML option, but find it interesting. Could someone comment on some of the practical applications of using the XML configuration file approach instead of using a SQL server table?

    EDIT: and what I mean is what the advantages of using XML config files that could not be done or be done better vs. using the SQL server table approach, in real world application.

    Thanks!

  • I have just completed DTS to SSIS conversion and the XML config file worked great for my application. It made deployment much easier that before.

  • I used to create packages as files on disk. And after development is completed I copy them to appropriate production directory.

    When creating configuration file (CF below) the only option I see is to set package CF with particular path_name.

    Is it possible to have package understand to work with CF located in the very same directory (along with package file) no matter what path was documented for CF?

    Or is it possible to change configuration file path without rebuilding the package?

    Thanks

    Yuri Rubinov

  • You can place the package configuration file in any location. What is important is for you to select the appropriate configuration file during deployment and to ensure the file can be read.

    Should the path of the configuration file be changed, then the package should be re-deployed using the configuration file in the new path.

  • I think I read some where that after adding a new environment variable that you have to reboot. Is that true?

Viewing 15 posts - 1 through 15 (of 25 total)

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