Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Simple Steps to Creating SSIS Package Configuration File Expand / Collapse
Author
Message
Posted Wednesday, May 13, 2009 9:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 8:23 PM
Points: 47, Visits: 171
Comments posted to this topic are about the item Simple Steps to Creating SSIS Package Configuration File
Post #716616
Posted Thursday, May 14, 2009 7:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:10 AM
Points: 1,176, Visits: 81
What do you do with the configuration file after you have it created and modified?
Post #716939
Posted Thursday, May 14, 2009 7:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:47 AM
Points: 472, Visits: 517
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!
Post #716944
Posted Thursday, May 14, 2009 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 12:47 PM
Points: 1, Visits: 10
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
Post #716950
Posted Thursday, May 14, 2009 7:56 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:47 AM
Points: 472, Visits: 517
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!
Post #716960
Posted Thursday, May 14, 2009 7:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:27 AM
Points: 554, Visits: 1,192
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.
Post #716966
Posted Thursday, May 14, 2009 8:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:47 AM
Points: 472, Visits: 517
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!
Post #716968
Posted Thursday, May 14, 2009 8:37 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Famson, thanks for the article. But, I hate using XML for configuration when you can store same in a configuration table.

SQL DBA.
Post #717019
Posted Thursday, May 14, 2009 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 8:23 PM
Points: 47, Visits: 171
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.
Post #717030
Posted Thursday, May 14, 2009 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:38 AM
Points: 4, Visits: 173
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.
Post #717095
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse