Refresh

Process Overview

SSIS packages on the ED-MSSQL Server are run.  A finder file is built -- for example a temporary table is loaded with edw_pers_ids of interest to the college.  This file is then joined against the core edw tables.  Data is extracted to a text file (“get”).  Local data is truncated.  Data is loaded (“put” via bcp).  The only transformation that occurs is in the limiting of rows loaded.  The refresh occurs weekly on Friday afternoons or on request after key post dates.

Key points: 

• Finder files are used to speed up the process and keep the logic centralized and easily adjusted
• EDW tables are mirrored in fields and datatyping for portability of sql and simplification of transformation
• Basic filters are applied, such as cur_info_ind = Y and coa_cd = 1
• Bulk Copy is used, however does not work on long text fields (e.g.  T_PCARD_TRAN_COMMENT)

 

Finder File SQL Queries

How to create a linked server on MS SQL Server (2000)

Sample Query across local and linked server