Susquehanna: custom export

[Stub: please expand]

In addition to the standard export, Susquehanna has a secondary export which runs after the standard export is finished.  The SQL Server Agent job Susqu Create Other .csv files is scheduled to run after the Extract 2 job is certain to be finished.

Susqu_Warehouse has additional temporary export tables defined which are populated by the Susqu Create Other .csv filesjob:

  • X_Activity
  • X_Interests
  • X_Parent (relationships)
  • X_Scores (unflattened)

 The job Susqu Create Other .csv files uses the following DTSX files for the additional export:

  • SusquScores.dtsx  
  • SusquParent.dtsx 
  • SusquInterests.dtsx 
  • SusquActivity.dtsx

It produces the additional zip file Interests,Parents,Scores, containing the following CSV files:

  • Susqu_Scores.csv
  • Susqu_Interests.csv
  • Susqu_Parent.csv
  • Susqu_Activity.csv 

 NOTE: the table joins for getting all the X_Parent data requires use of MSCRM rather than Warehouse, since views present before the changes in the Export/Analytics jobs are no longer there. 

FROM [Susqu_Warehouse].[dbo].[Xport] AS x 
INNERJOIN [Susqu_MSCRM].[dbo].[FilteredContact] AS fc ON x.Relationship_01_C422PersonID = fc.contactid 
LEFTJOIN [Susqu_MSCRM].[dbo].[FilteredA422_PersonOrg] AS po ON po.a422_personid =x.Relationship_01_C422PersonID