ETL - BI-Integration - Interfase

Last Updated 5/24/2021

Overview
Location
Schedule
Details
Change Log
Troubleshooting Notes

Overview:

The ETL - BI-Integration - Interfase moves data from JobConnection (also known as Interfase) to the dataMarq staging database.  The dataMarq staging database serves as the data lake for Marquette.  Tables are pulled from the source with no manipulation, the only addition being audit fields (see below).

The purpose of this ETL is to move raw data from JobConnection to MyJob without any kind of transformation. This is governed by Finance, and usually controlled by MyJob Developers in ITS.

Location:

1) SSIS Catalog > ETL-dataMarq > DS-Interfase = This project handles moving data from source Interfase db server to dataMarq staging database.

2) SSIS Catalog > BI-Integrations > DW-Interfase = This project contains all the packages for moving data from dataMarq to MyJob.

Schedule:

The job runs as a standalone SQL Agent job:

ETL - BI-Integration - Interfase – Daily at 5pm

Project and Package Details:

Each individual table in this staging project has its own package. Packages follow the same basic flow as described below.

Project Parameters

dwStgConnStr: connection string to datamarq_staging. Used for the connection string in the connection manager to make that connection dynamic

interfaseConnStr: connection to the Interfase instance

mjConnStr: connection to the MyJob instance using Attunity connector (read-only)

mjOleDbServer: connection to the MyJob instance using OLE DB connector (read+write)

 

Package Parameters

V_STAGE_SOURCE: the stage source code that gets entered into the audit fields. It is INTERFASE for these jobs.

 

ETL-dataMarq\DS-Interfase Package Flow

The main runnable package is named interfase_load_all.dtsx which contains a number of packages that are individually designed using the actual source & dest table name as below-

  1. Truncate table – truncates the datamarq staging table for the corresponding package
  2. Load data to Staging- loads data from MyJob to dataMarq staging destination table adding the standard audit fields

BI-Integrations\DW-Interfase Package Flow

The main runnable package is named interfase_load_all.dtsx which contains a number of packages that are individually designed using the actual source & dest table name as below-

  1. Truncate table – truncates the MyJob table for the corresponding package
  2. Load data to MyJob - loads previous package staged data to MyJob destination table as is

 

 

Change Log:

Date Developer Change Record
6/23/2021 Imran Initial Documentation


Troubleshooting Notes:

  1. Changes to source tables

    SSIS is very metadata sensitive and any changes to the source table can cause the packages to fail. We use fully qualified queries - as opposed to Select * - in order to help mitigate this issue. Patches to source systems should be tested in the INT environment to make sure current packages continue to work.

  2. Connectivity Issues

    In the event a MyJob instance is down or the write step is not working properly the OLE DB connector needs to be tested to make sure it is working properly. Also because it is doing a write command in MyJob only the APPS user should be used which has all permissions to do so. Be extra sensitive using this account as it can accidentally change or truncate other tables as well.