We attempted to implement the Chapter 2 Framework from the Knight, Vermeen, Moss, Hackney book on SSIS 2008: Problem Design Solution. We see a couple of problem areas (at least) in our interpretation of the guidelines from the book. 1. We are attempting to have one connection string per server: dev/prod 2. We have set up environmental variables per server: dev/prod 3. We have set up XML Package Variables per server: dev/prod 4. We have set up System Variables per server in SQL Server: dev/prod Problem 1: We've had to manually key in the common connection string to all packages on a server environment in the component properties area (control flow; not data flow--ok there) Shouldn't the connection property find the connection that we've instantiated and provide us with a pull down to select it? The components are un-editable after we select the common connection string, but they still run properly in our SQL Server Agent Job. Problem 2: The dev server variable overlays the prod server variable information duringprod component editing We are'nt sure if these problems are related. Here is our package variable: \Package.Variables[User::connMedcDwProd].Properties[Value] Here is our DTSconfig file; we have a few variations of this that we've tried. <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="MEDC\SSIS Team" GeneratedFromPackageName="SSIS_MEDC_Template" GeneratedFromPackageID="{EA3D047E-765D-4A2B-B8C6-0D8AA497B699}" GeneratedDate="9/20/2010 04:00:00 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[MEDC-SSIS\SSISSQL2008.MedcDwProd.sa1].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=MEDC-SSIS\SSISSQL2008;User ID=sa;Initial Catalog=MedcDwProd;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[MEDC-SSIS\SSISSQL2008.MedcDwProd.sa1].Properties[ServerName]" ValueType="String"> <ConfiguredValue>MEDC-SSIS\SSISSQL2008</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[MEDC-SSIS\SSISSQL2008.MedcDwProd.sa1].Properties[InitialCatalog]" ValueType="String"> <ConfiguredValue>MedcDwProd</ConfiguredValue> </Configuration> </DTSConfiguration> I've validated the SSIS_Configurations table for data similarities in both dev and prod. dev has: CommonConfigurations Data Source=MEDC-SCCM-SQL\SCCMSQL2008;User ID=sa;Initial Catalog=MedcDwDev;Provider=SQLNCLI10.1;Integrated Security=SSPI; \Package.Variables[User::connMedcDwDev].Properties[Value] String Prod has 2, since I've been experimenting with the SSIS_MEDC piece. CommonConfigurations SSIS_MEDC \Package.Connections[SSIS_MEDC].Properties[Name] String ***(Experimental) CommonConfigurations Data Source=MEDC-SSIS\SSISSQL2008;User ID=sa;Initial Catalog=MedcDwProd;Provider=SQLNCLI10.1;Integrated Security=SSPI; \Package.Variables[User::connMedcDwProd].Properties[Value] String Our goal is to make one change per server for the connection strings. Some of this work was manually performed. Anyone want to take a critical look at our framework? Many Regardx, Methody

Only one connection string should be in the config file and the config file shoiuld be updated based on where you are running your package on.Arthur My Blog