Post reply

Simple Steps to Creating SSIS Package Configuration File

  • May 13, 2009 at 9:19 pm

    #204300

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

  • May 14, 2009 at 7:43 am

    #994516

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

  • May 14, 2009 at 7:46 am

    #994521

    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!

  • May 14, 2009 at 7:50 am

    #994526

    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

  • May 14, 2009 at 7:56 am

    #994535

    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!

  • May 14, 2009 at 7:59 am

    #994540

    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.

  • May 14, 2009 at 8:00 am

    #994542

    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!

  • May 14, 2009 at 8:37 am

    #994585

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

    SQL DBA.

  • May 14, 2009 at 8:47 am

    #994595

    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.

  • May 14, 2009 at 9:46 am

    #994649

    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.

  • May 14, 2009 at 12:54 pm

    #994805

    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!

  • May 14, 2009 at 2:14 pm

    #994877

    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.

  • May 15, 2009 at 2:42 pm

    #995691

    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

  • May 15, 2009 at 3:07 pm

    #995710

    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.

  • May 15, 2009 at 4:56 pm

    #995759

    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