Let's assume you have created a series of custom Tables within both your Runtime Databases: Test and Production. When you run in the test environment, you want to read information from the Test Runtime Database and when you run in the production environment, you want to read information form the Production Runtime Database, without having to change any Data Item default values or configuration in your Solution.
You can create an XML file, to be stored in the same directory path on your different BPM Servers, Test and Production, containing information that allows you to build the connection strings for your two different Runtime Databases. Within your top-level Process, you then use a Read XML Document Activity to load the information at execution time, complete with an Expression to ensure you have a single Data Item containing the value of the Connection String for a particular Database. When you create your ODBC Resource, use this Data Item to connect to the correct Runtime Database and complete your work.
For example, let's use the XML file:
<Provider>SQLOLEDB.1</Provider>
<Password>[Password]</Password>
<PersistSecurityInfo>True</PersistSecurityInfo>
<UserID>[SQL User Name]</UserID>
<Database>[Runtime Environment Database Name]</Database>
<SQLServer>[SQL Server Name]\[Instance Name]</SQLServer>
We save this XML as a file on both servers at C:\Program Files\Bluespring BPM 5\Runtime\InitializeValues.xml. Then, our Read XML Document Activity is configured as:
Notice how the Input Source is the same location on each Runtime Server, and the directory path is read locally from the Runtime Server executing the Process, so you can read different values into your running Process, based upon the files being on different Runtime Servers.
Then, within the Data Item Assignments, we map the Destination Data Item: RuntimeDatabaseConnectionString to an Expression: Concat("Provider=",Solution.DataItems.Provider,"Password=",Solution.DataItems.Password,"Persist Security Info=",Solution.DataItems.PersistSecurityInfo,"User ID=",Solution.DataItems.SQLUserID,"Initial Catalog=",Solution.DataItems.Database,"Data Source=",Solution.DataItems.SQLServer)
At execution time, the Data Item: RuntimeDatabaseConnectionString will then have a value of: Provider=SQLOLEDB.1;Password=[Password];Persist Security Info=True;User ID=[SQL User Name];Initial Catalog=[Runtime Environment Database Name];Data Source=[SQL Server Name]\[Instance Name]
This Data Item can then be configured in the ODBC Resource:
When you want to interact with the Database, you select this Resource in your Activity:
Your Database Activity will use the dynamically generated Connection String in the Data Item: RuntimeDatabaseConnectionString to connect to the Runtime Database specified in the XML File from the Runtime Server, which contains different values for the Runtime Environment storing the XML file. Effectively, you ensure that your Connection String is dynamically generated based upon the Runtime Environment, either Test or Production.
