I have 2 servers. DWTEST and DWSQL1. I developed my packages connecting to DWTEST in the design time. I have 32 packages, one of them is the master one and the others are child packages.
package PM calls P1, P2, P3,.......P31
i have 2 data source in my project. RDS and S0, both setup to connect DWTEST in the design. i defined rds_connection_string and s0_connection_string variables in my master package and created an XML config file and put those variables into that config file.
C:\PROJECTS\DEVELOPMENT\SSIS Configurations\SERVER.dtsConfig
<DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="TC\tsql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWTEST;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWTEST;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
built the project into directory C:\PROJECTS\DEVELOPMENT\S1_REUSABLE_DATA_STORE\S1_REUSABLE_DATA_STORE\bin\Deployment
when i deploy my packages into DWTEST and run them as a SQL Server Agent job they work because data sources are signing to DWTEST already.
when i am deploying them(using the same manifest file in the deployment directory i wrote above) into the server DWSQL1, i select config file source as:
C:\PROJECTS\TESTING\SSIS Configurations\SERVER.dtsConfig
and update data source strings in it to DWSQL1:
- <DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="TC\tsql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
When i run SQL Server Agent Job i defined in DWSQL1(forcing it to use DWSQL1 data source in step definition), my master package PM connects to DWSQL1, and my child packages don't use my config definitions and connect to DWTEST which is defined in the design time.
How can i force all my packages to connect DWSQL1 server using the same built files in deployment directory WITHOUT editing my packages(recreating config files or changing the definition of Data Sources and then built the project again) in Visual Studio?
Thanks to you for reading my long question...
I got a solution for my problem from a friend.
Now, i am using XML configuration file in my Master package, and Parent Package Variables in child packages. Child packages gets their data source connection strings from these variables.
Then i am keeping two different copies of XML config file in different servers(DWTEST and DWSQL1) physically.
In design environment my data source conenctions setup to connect to DWTEST. I build my project, then using the Deployment Manifest i deploy my packages into server DWTEST.
In SQL Server Agent Job Definition, in Step definition, at Configurations Tab page i add a config file and navigate to the config file stored in DWTEST(which is using DWTEST in values) and it works in DWTEST. all my child packages also connects to DWTEST.
Then i deploy my packages into DWSQL1 server using the same Deployment Manifest without editing anything in visual studio an building the project again.
in DWSQL1, in SQL Server Agent Job Definition, in Step definition, at Data Sources Tab page i check the checkboxes and edit the strings to connect to DWSQL1, and at Configurations Tab Page, i add a config file and navigate to the config files stored in server DWSQL1(which is using DWSQL1 in values). Then my packages are all working well and connecting to DWSQL1.
So it seems my problem has been solved.
|||
I was just thinking, why not make all the packages p1,p2...p30 point to the same configuration file as that's used by PM?
When you deploy using deploymentmanifest, you'll only change the config file once, and since all the packages are looking at the same package, they'll pick up the new settings.
That way, in case you make changes to the config file on DWTEST during development, you don't need to worry about forgetting to update the xml file in dwsql.
Just thinking out loud. :)
sql
