Back to Home

SSIS Process for Checklist Import

Overview

The SSIS package purpose is to help eliminate manually importing new checklists (which can be 1000 of records), which will clean up any white spaces, validate fields, and every field is valid, import the data to the appropriate tables associated with Checklists. If there is just 1 invalid data, the whole job will export the bad data to another .csv file and automatically email business the .csv file.

TL;DR: SSIS package will only insert new checklists if all data is valid, validaton will clean up any extraneous spaces, check if field is valid

Tables

• smart.p11_Checklist_Import
  • Initially all data will import here regardless of datatype. DataType for all fields are VARCHAR(MAX)
• smart.p11_Master_Checklist
  • Inserts new Checklists
• smart.p11_SectionList
  • Inserts new Sections tied to new Checklists
• smart.p11_FieldList
  • Inserts new FieldList tied to new Sections and new Checklists
• smart.p11_SourceDocument
  • Some checklists may or may not have a SourceDocument, but will check against smart.p11_Checklist_Group table's QuickTipNo value to know which level it should insert into, if it exists
    • QuickTipNo = 1; Section Level
    • QuickTipNo = 2; Field Level

Stored Procedures

• smart.p11_Load_Checklist
  • The clean up of data and validation occurs within this stored procedure
• smart.p11_ChecklistNotification
  • An email notification will go out, if there is at least (1) invalid data
  • email includes network path to see the file

SSIS Flow

• EXEC SQL TASK: Initialize Checklist Import Table
• DATA FLOW TASK
  • Load New Checklists to smart.p11_Checklist_Import table
• EXEC SQL TASK: LoadChecklist
  • Validates all fields
    • Loads new checklists if successful
    • Exports data to .csv file if data is invalid and sends out email to where the file path
• EXEC SQL Task: CheckInvalidCount
  • Conditional select statement to check if there is invalid data
• DATA FLOW TASK: ArchiveToExcel
  • If InvalidCount equals "0", then the Original Import Checklist .csv file gets archived in an Archive folder, with a Date TimeStamp appended (QUADChecklistImport_YYYY_MM_DD_hh_mm.csv)
• DATA FLOW TASK: ExportBadDataToExcel
  • If InvalidCount is greater than "0", then data is exported to a .csv file with a Date TimeStamp appended (QUADChecklistExportBadData_YYYY_MM_DD_hh_mm.csv)

Note: Files are currently in the SOBI_SHARED_FOLDERS under "Checklist"

Process

• After receiving the file from business, rename it to QUAD Checklist Import.csv
• Move the file to Checklist Folder, located in the SOBI_SHARED_FOLDERS location
• If file appears in Archive Folder, then job ran successfully
• If file returns as QUADChecklistExportBadData in Checklist folder, then job detected bad data. An email should be sent out to business. Have them review and resend corrected data file.

Extra Considerations

Business will occasionally request to add SOBIErrorCategory value. Our current process cannot handle it, so we will need to create an overnight SAFE script to update the values after the job successfully loads the new checklists.

Table: smart.p11_SectionList

Script 1

• Copy SOBIErrorCategory values in column B, SectionTitle in Column D
  • See template file: Excel Template
• Paste into new query window in SQL

• Highlight extra white space and do a Ctrl + H and replace all excel tabbed spaces with just an empty value.

  • Note: this will not clean up the white spaces using the LTRIM, RTRIM function.
  • 

• (1) First update script cleans up any extra white spaces our Find and Replace did not catch

• (2) Adds the correct LookUpNo int value with the LookUpTitle SOBIErrorCategory value business provides
• (3) Create a new temp table #tempSectionList, to match SectionTitle with the correct SOBIErrorCategoryNo
• (4) Actual update insert into the production database table