In general, packages should:
- Use the connection BBETL_DB_CONN_OLTP for read-only extraction. (This will connect to the snapshot, if Use Database Snapshots is true. Otherwise, it will connect to the OLTP DB and your custom packages will not be taking advantage of the new Snapshot feature.)
- Use BBETL_DB_CONN_OLTP_WRITEBACK for read/write. (This will connect to the OLTP DB and should be used for packages that need to write data back to the OLTP DB)
The templates BBDW_DIM_TEMPLATE.dtsx and BBDW_FACT_TEMPLATE.dtsx show the required package configuration, including the BBETL_DB_CONN_OLTP_WRITEBACK connection.
To add the BBETL_DB_CONN_OLTP_WRITEBACK connection to a package, follow the example in the templates. The step-by-step process would be as follows:
- Add a new OLE DB connection named BBETL_DB_CONN_OLTP_WRITEBACK to the package
- From the SSIS menu, click Package Configurations. Click Add. Click Next.
- Under Configuration type, choose XML configuration file
- Select Configuration location is stored in an environment variable
- In the Environment variable field, put BBDW_ETL_WRITEBACK_CONFIG_PATH
- Click Next