June 19, 2013 at 9:43 am
I need advice on how best to use package configurations. I understand the mechanics of creating a package configuration. I have been using SSIS part time for about 3 months and am completely self taught, so I may be have missed something that those with formal training learned on day 2.
I have a development instance and a production instance. The database structures are exactly the same between the two instances.
I develop on the development instance or on my PC with data source connections to the development database. I place the ConnectionString, InitialCatalog and ServerName in an XML configuration file package configuration.
When I deploy to the development instance, the package executes as expected. I created another package Configuration for my production instance, think I never have to edit the configuration again until something new is added to the configuration.
To deploy to production, (I store the packages in SQL Server)
1. I copy the manifest, dtsx files and production dtsConfig files to the production instance.
2. I double-click the manifest to start the Package Installation Wizard. I do check "Validate Packages after installation"
3. I review the package configuration values at the "Configure Packages" step. If the connection strings are pointing at the development instance, I edit them to use the production instance.
4. When I execute the package on production, it updates the data on the development instance. Therefore, my production configuration was not used and the development configuration file was used or no configuration file was used.
What is the best strategy to use XML file package configurations?
Do I have a single package configuration file stored with the BIDS project and edit it before building the package? That seems to defeat the purpose of configuration files if I have to edit it each time I deploy the package.
Should I have a package configuration file stored locally on each database server with the appropriate values? This is not optimal because the configurations should stored with the BIDS project.
In the SQL Server job step properties, there is a configurations tab. Should the configuration be displayed there (that tab is blank on my jobs) or is that another way to override an existing package configuration?
June 20, 2013 at 5:35 am
The package configuration files exist in each environment. So for instance, if you are using configuration files on each servers, you would have 2 files, one on development with the connection string pointing to the development server / database. A second file would be on production, and that configuration file would have the connection string details for production.
June 20, 2013 at 7:42 am
Make sure that the your package knows where to find the proper config file. It must be the exact same path in either environment like C:\configFilepath\configfile.dtsconfig. If the path is different in each environment, then it won't work unless you are using something like an environment variable as your base configuration pointer.
One other thing to be aware of, that took a couple of days from my life, is where exactly are you executing the package? If you are logged into SSMS with a remote connection to the production server and executing the package from the production environment by right clicking and executing the job, then the job will read the config from YOUR PC. If you want to check if it is reading the proper config it must be executed from the proper environment through a SQL Agent schedule or some other means where your personal PC is not part of the equation.
Good luck.
June 21, 2013 at 3:53 pm
It appears the best practice is to have a single package configuration file name with instances of the the package configuration stored on each server. This may have been part of my issue as I had the configurations stored on a file share with the source code.
Has anyone tried to build a master package configuration file that all SSIS projects and packages could access? I suspect this is not feasible as an item referenced in one package that does not exist in another package will casue SSIS to error.
June 21, 2013 at 4:04 pm
bill.lawler (6/21/2013)
It appears the best practice is to have a single package configuration file name with instances of the the package configuration stored on each server. This may have been part of my issue as I had the configurations stored on a file share with the source code.Has anyone tried to build a master package configuration file that all SSIS projects and packages could access? I suspect this is not feasible as an item referenced in one package that does not exist in another package will casue SSIS to error.
It's not really feasible. One of the main uses for configurations is to allow for environmental modifications without having to open the primary package. This allows for code to be 'hardened' in change control with only ini file equivalents needing modification. My current location uses SQL tables for config control, but I've never tried to keep a consistent configuration across multiple environments. I've always used it the exact opposite way, to keep the code consistent and adjust the configuration across environments.
If you think of configurations as .ini files, and not source code, it may make your life a lot easier.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply