Simple Steps to Creating SSIS Package Configuration File

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lanre Famuyide » Simple Steps to Creating SSIS Package...

25 posts, Page 1 of 3

1 2 3 »»»

Simple Steps to Creating SSIS Package Configuration File

Rate Topic

Display Mode

Topic Options

Author

Message

Famson

Posted Wednesday, May 13, 2009 9:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 18, 2013 2:00 PM
Points: 35, Visits: 163

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

Post #716616

jeberhard-623651

Posted Thursday, May 14, 2009 7:43 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 886, Visits: 58

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

Post #716939

Meow Now

Posted Thursday, May 14, 2009 7:46 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:15 PM
Points: 471, Visits: 485

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

jeffa00

Posted Thursday, May 14, 2009 7:50 AM

Forum 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

Meow Now

Posted Thursday, May 14, 2009 7:56 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:15 PM
Points: 471, Visits: 485

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

Bradley Deem

Posted Thursday, May 14, 2009 7:59 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:22 AM
Points: 551, Visits: 1,150

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

Meow Now

Posted Thursday, May 14, 2009 8:00 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:15 PM
Points: 471, Visits: 485

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

SanjayAttray

Posted Thursday, May 14, 2009 8:37 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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

Famson

Posted Thursday, May 14, 2009 8:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 18, 2013 2:00 PM
Points: 35, Visits: 163

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

jbowers

Posted Thursday, May 14, 2009 9:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:47 AM
Points: 3, Visits: 123

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 »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions