Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple Steps to Creating SSIS Package Configuration File


Simple Steps to Creating SSIS Package Configuration File

Author
Message
Famson
Famson
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 230
Comments posted to this topic are about the item Simple Steps to Creating SSIS Package Configuration File
jeberhard-623651
jeberhard-623651
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 Visits: 193
What do you do with the configuration file after you have it created and modified?
Kick6Tiger
Kick6Tiger
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 761
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!
jeffa00
jeffa00
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Kick6Tiger
Kick6Tiger
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 761
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!
Bradley Deem
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1248
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.
Kick6Tiger
Kick6Tiger
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 761
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!
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Famson, thanks for the article. But, I hate using XML for configuration when you can store same in a configuration table.

SQL DBA.
Famson
Famson
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 230
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.
jbowers
jbowers
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 216
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search