SSIS Deployment Files

Within your Business Intelligence project, you can maintain two files for use by the deployment utility and ETL process:

BBDW_DeletedIDTables_EXT.txt
BBDW_PackageList_EXT.txt

These files respectively contain a list of deleted ID tables and a manifest for the SSIS packages.

Note: The deployment utility does not require that these files be located in a project. But placing the files in a Business Intelligence project along with your SSIS packages is a convenient way to maintain the files.

BBDW_DeletedIDTables_EXT.txt contains comma separated data as follows:

"Enabled","SOURCEAUDITTABLE","MARTTABLE","MARTTABLECOLUMN"

"1","EVENTAUDIT","BBDW.DIM_EVENT_EXT","EVENTSYSTEMID"

A value of "1" for "Enabled" corresponds to TRUE. "SOURCEAUDITTABLE" is the source table in the OLTP database. "MARTTABLE" and "MARTTABLECOLUMN" are the table and column in the data warehouse database. BBDW_DeletedIDTables_EXT.txt maintains a list of audit tables for the ETL process to reference to ensure anything deleted from the OLTP database is also deleted from the warehouse.

The Master ETL Package (BBDW_ETL.dtsx) performs this logic in the Sub-Package Processing sequence when it executes the BBDW_DELETEDIDS.dtsx package. Most of the actions in this sequence concern the out-of-the-box packages. But the sequence also handles the BBDW_DeletedIDTables_EXT.txt file in addition to the corresponding BBDW_DeletedIDTables.txt file for the out-of-the box packages. For more information, see ETL Process.

BBDW_PackageList_EXT.txt contains comma separated data as follows:

"Enabled","Package"

"1","BBDW_DIM_EVENT_EXT.dtsx"

Again, a value of "1" for "Enabled" corresponds to TRUE. "Package" is the filename for the SSIS package to run.

Config File, Environment Variable, and Deployment

Configuration information for SSIS packages (ETL) is contained in a .dtsConfig file. An environment variable on SQL Server points to the file. The variable is BBETL_BBInfinity_RPT_BBDW. You can maintain a personal configuration for development purposes. Configuration information includes connection properties for the packages.

Configured values in the .dtsConfig correspond to variables and properties in Blackbaud Data Warehouse SSIS packages. For example, one configured value is the connection string for the transactional database connection. Packages for Blackbaud Data Warehouse have three connections:

BBETL_DB_CONN_OLTP: connects the package to the transactional database
BBETL_DB_CONN_DW: connects the package to the data warehouse
BBETL_DB_CONN_OLAP: connects the package to the OLAP cube

When you run the deployment utility, the utility copies the SSIS packages from your application folders and changes the configuration by pointing to the BBETL_BBInfinity_RPT_BBDW environment variable which references the BBDW_ETL_CONFIG.dtsConfig file. The development .dtsConfig file is independent of the deployment .dtsConfig. To create a development configuration, you copy the version in the application folder (not the SQL Server folder), modify the .dtsConfig file, and create a separate environment variable to point to the .dtsConfig file.

After you create a personal configuration, when you edit packages that are not deployed to SQL Server, the connections are based on connection properties set in your personal .dtsConfig file. When you run the deployment utility, your personal .dtsConfig file is ignored. Instead, new settings based on your selections in the utility wizard are used by the .dtsConfig file on SQL Server.

Copy Config File

Copy BBDW_ETL_Config.dtsConfig to BBDW_ETL_Config.{your name}.dtsConfig. In other words create a personal copy of BBDW_ETL_Config.dtsConfig with a different name.

There is a copy of BBDW_ETL_Config.dtsConfig is located in MSBuild\Datamarts\BBDW\SSIS. But, after a deployment, the utility creates a version in your SQL Server folder. For example:

C:\Program Files\Microsoft SQL Server\100\DTS\Packages\BBETL\BBInfinity_RPT_BBDW

The settings in the SQL Server version are based on settings in the deployment utility.

Modify Personal Config File

With a text or XML editor, open your personal copy of BBDW_ETL_Config.dtsConfig ( BBDW_ETL_Config.{your name}.dtsConfig).
Replace instances of the server name as applicable.
Replace instances of database names as applicable.
Save the file.

Set Environment Variable

From your machine's system properties, create an environment variable named BBDW_ETL_CONFIG_PATH.

From your desktop or Start menu, right-click Computer or My Computer.
Click Properties.
Click Advanced system settings. The System Properties screen appears.
Click Environment Variables.
Click New.
In Variable name, enter BBDW_ETL_CONFIG_PATH.
In Variable value, enter the path to your personal .dtsconfig file such as C:\Program Files\Blackbaud\bbappfx\MSBuild\Datamarts\BBDW\SSIS\BBDW_ETL_CONFIG.Personal.dtsConfig.
Click OK for each screen.

When you run the deployment utility, the utility copies the SSIS packages and changes the configuration of the packages to point to a new environment variable and a different .dtsConfig file. The development .dtsConfig file is independent of the deployment .dtsConfig file.