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?