Replicating Encompass Data Connect Data with SSIS

With Encompass Data Connect Replication, you can quickly access large amounts of near-real time loan data that's stored in the cloud, thus enabling you to leverage your existing technology to build your own intelligence on top.

As part of the data load, Encompass Data Connect encrypts and stores Personally Identifiable Information (PII). SQL Server Integration Services (SSIS) decrypts PII fields to load data from Encompass Data Connect to your on-premise database. For more on PII decryption, view Decrypting Encompass Data Connect PII Fields.

To download SSIS SQL files to be used to help create tables for Encompass Data Connect, visit the Encompass Data Connect - SSIS SQL Files knowledge base article in the Resource Center and click the attachment.

Please note that SQL Server Integration Services (SSIS) is a part of SQL Server and is supported by Microsoft.

From Encompass Data Connect (Postgres) to Your Database (MS SQL Server)

To Replicate Data:

  1. To install SSIS and integration tools, visit https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services.

  2. To download and install Postgres OLEDB driver, visit http://www.pgoledb.com/.

  3. Before creating the SSIS project, use mssql_DataModel.sql to create tables in SQL Server if you haven't already.

  4. In SQL Server data tools, create an SSIS project:

    • Add Data Flow Task.

    • From Data Flow Task, add OLEDB source.

    • Configure OLEDB source to connect to Postgres using the correct driver.

    • Add Data Conversion Component.

    • Add OLEDB destination, pointing to your SQL Server database.

    • Map new columns to non Unicode type (example: DT_STR instead of DT_WSTR).

  5. Click Save.

  6. Click Start - Data from Encompass Data Connect is copied to your server.

From Encompass Data Connect (MS SQL Server) to Your Database (MS SQL Server)

To Replicate Data:

  1. To install SSIS and integration tools, visit https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services.

  2. Before creating the SSIS project, use mssql_DataModel.sql to create tables in SQL Server if you haven't already.

  3. In SQL Server data tools, create an SSIS project:

    • Add Data Flow Task.

    • From Data Flow Task, add OLEDB source pointing to the Encompass Data Connect SQL Server.

    • Add OLEDB destination, pointing to your SQL Server database.

  4. Click Save.

  5. Click Start - data from Encompass Data Connect copies to your server.

Data Decryption

All PII fields are encrypted in Encompass Data Connect. You can decrypt and store these fields in SQL Server database.

To Decrypt PII Fields:

  1. Use mssql_nopii_DataModel.sql to create tables with correct field types.

  2. In SQL Server data tools, create an SSIS project:

    • Add Data Flow Task.

    • From Data Flow Task, add OLEDB source.

  3. Add Script component - Connect the output of OLEDB source to this component.

  4. To download BouncyCastle C# Ext library supporting IDEA algorithm and extract DLL file, visit http://www.bouncycastle.org/csharp/.

  5. In properties of DLL file, set to unblocked.

  6. Open script component:

    • Select columns to decrypt.

    • In the output section, create the same number of columns as you selected for decryption.

    • Change the data types of these output columns to DT_STR with required length.

  7. Click Edit Script to open script editor - a new VB studio opens with the script class selected.

    • In Solution Explorer, right-click on the references section.

    • Add reference to extracted BouncyCastle DLL.

    • Edit the script as the example given in ssis_decrypt_script.txt.

    • Save and close.

      Before closing, ensure you can access your private key and have the passcode for the public key for Encompass Data Connect.

  8. Add OLEDB destination, pointing to your SQL Server database.

  9. Connect script component to OLEDB destination.

  10. Map columns correctly - Outputs of script component should be mapped correctly to destination fields

  11. Click Save.

  12. Click Start - Data is decrypted to actual values.

SSIS Incremental Load

To download a file with an SSIS sample for incremental load, visit the Encompass Data Connect - SSIS Incremental Load knowledge base article in the Resource Center and click the attachment.