SSIS Processes

DDD

1. Fetch_Learner_DDD.dtsx 
	No SP's or queries.
	Downloads files from FTP
	Get date, etc from file name
	Move to download directory

2. Import_Learner_DDD.dtsx	
	1. Get Files
	2. Set FileDataSourceType
	3. Get DataImportID
		1. function dbo.pnewdataimportid
			Update/Insert (Get DataImportID)
				clickstaging.dbo.datacontract
				clickstaging.dbo.dataimport			
	4. Import Learner File				
		Update/Insert
			clickstaging.dbo.learner (Import Learner File)	
	5. Import Changed File
		Update/Insert
			clickstaging.dbo.learnerdddchangedid (Import Changed File)
	6. Import Merged File
		Update/Insert		
			clickstaging.dbo.learnerdddmergedid (Import Merged File)
	7. Update DataImport (Success)
		1. function dbo.pupdatedataimport
			Update (Update DataImport (Success))
				clickstaging.dbo.dataimport
	8. Move Files
	9. Inc FileCount
	10. Generate DDD File Check
		3. Reports_Generate.dtsx
			1. function clickdw.reporting.pgeneratereports (in progress)
			2. function clickdw.dbo.fgetrelatedhierarchyrootentityid

3. Import_learner_DDD_Finalize.dtsx
	1. function internal.pupdatedddids
		1. procedure internal.pdataimportloginsert
		2. function internal.pupdatedataimport
		3. function internal.pnewdataimportid
	2. procedure internal.pupdatesourcelearner
	3. procedure internal.pupdatelearnerfromsourcelearnerfirstusabledata
		1. procedure internal.pupdatelearnerfromsourcelearner
		2. procedure internal.pupdatesourcelearner
			1. procedure internal.pdataimportloginsertcount
			2. procedure internal.ptemporalstatus
			3. procedure internal.pdataimportloginsert

4. TransferDW_Learner_Registrations.dtsx
	1. function dbo.plearnersregisterfromsourcelearner
		1. function dbo.pnewdataimportid
		2. function dbo.pupdatedataimport
	2. function internal.pnewdataimportid
	3. procedure internal.ptransferlearner
	4. procedure internal.pupdatelearnerfromsourcelearner
	5. function internal.pUpdateDataImport
	6. procedure internal.pgenerateusernames
		1. procedure internal.pgenerateusernamesmatific
		2. procedure internal.pgenerateusernamesreflectivelearning

CEMIS

1. Import_learner_cemis_simple.dtsx
	1. function dbo.pnewdataimportid
	2. function reporting.pvalidatecemislearner
	3. procedure dbo.pupdatelearnercemis
	4. function dbo.pupdatedataimport
	5. procedure internal.pupdatesourcelearner
	6. procedure internal.pupdatelearnerfromsourcelearnerfirstusabledata
	7. procedure internal.pupdateschooldatasource
	8. Reports_Generate.dtsx (see below)

2. Reports_Generate.dtsx
	1. function clickdw.reporting.pgeneratereports (in progress)
	2. function clickdw.dbo.fgetrelatedhierarchyrootentityid

3. TransferDW_Learner_Registrations.dtsx (same as for DDD)

Reading Eggs
1. FetchImport_AWS-S3-CSV.dtsx
	1. Get Fetch Info
		Get credetials login S3 bucket - SQL query
	2. Get DataImportID
		1. function clickstaging.dbo.pnewdataimportid
	3. Import
		SSIS Script process (in progress - Lloyd)
	4. Update DataImport (Success)
		1. function clickstaging.dbo.pupdatedataimport

2. TransferDW_StoredProc.dstx
	1. Get Imports to Transfer
		SQL Query
	2. Transfer
		1. Get STAGING Data Import Values
			SQL Query
		2. pNewDataImportID
			1. function internal.pnewdataimportid
		3. pTransfer
			1. procedure clickdw.internal.ptransferparentconsenthomelearning (in progress) (User::StoredProc)
				1. function clickdw.internal.pdataimportlogtext
		4. pUpdateDataImport Success
			1. function internal.pUpdateDataImport

3. Export_Matific_Registration.dtsx
	1. Set Variables
		SSIS Script
	1. School Extract
		Source: clickdw.internal.pextractmatificregistrationschoollist '', 0, 1 (to be done)
		Destination: Flat file (school file - C:\OMS\DATA\EXPORTS\READING EGGS\TEACHER_EXTRACT__.csv)
	2. Learner Extract
		Source:clickdw.internal.pextractmatificregistrationlearnerlist '', 0, 1 (to be done)
		Destination: Flat file (learner file - C:\OMS\DATA\EXPORTS\READING EGGS\LEARNER_EXTRACT__.csv)
	3. Update Vars & Copy
		SSIS Script


4. Reports_Generate.dtsx
	1. function clickdw.reporting.pgeneratereports (in progress)
	2. function clickdw.dbo.fgetrelatedhierarchyrootentityid

5. Import_ReadingEggs_LearnerList.dtsx
	1. Get Files
		SSIS Script
	2. For Each File
		1. Get DataImportID
			1. function internal.pnewdataimportid
		2. Import File
			Source: C:\OMS\Data\Import\ReadingEggs Learner List\200128 Gauteng North RE CSV export.csv
			Destination: clickstaging.dbo.readingeggslearnerlist
		3. Update DataImport (Success)
			function dbo.pupdatedataimport
		4. Move Files
			SSIS Script

Matific
1. Import_CSV.dtsx
	1. Get Data Source Values
		SQL Query - SELECT FROM clickstaging.dbo.ExternalDataSource
	2.For Each Data Source
		1. Get Files 
			Source: C:\OMS\Data\Import\Performance - DDD\Gauteng_Granular_2019.csv
			SSIS Script
		2. For Each File
			1. Get DataImportID
				1. function internal.pnewdataimportid
			2. Import
				SSIS Script process (in progress - Lloyd)
			3. Validation
				1. pValidateParent2 (to be done)
				2. Destination: Flat file (C:\OMS\Data\Import\Performance - DDD\Gauteng_Granular_2019.csv.errors)
			4. Update DataImport (Success)
				function dbo.pupdatedataimport

2. TransferDW_StoredProc.dtsx (Same as Reading Eggs)

Import Masterlist
1. Import_Masterlist.dtsx
	1. Get DataImportID
		function dbo.pnewdataimportid
	2. Import File
		1. Update clickstaging.dbo.masterlist from source file
	3. Update DataImport (Success)
		function dbo.pupdatedataimport		
	4. Move files
		SSIS script
2. Run an SP: Exec ClickDW.internal.pTransfer @SourceDataImportID=X, @TranAction="R" (to be done)
	internal.pTransferMasterList (to be done)

Activate Schools / Grades per Program (Activate / Launch / Add School – Import)
1. Import_Environment.dtsx
	1. Get DataImportID
		function dbo.pnewdataimportid
	2. Import File
		1. Update clickstaging.dbo.environment from source file
	3. Update DataImport (Success)
		function dbo.pupdatedataimport		
	4. Move files
		SSIS script		
2. Run an SP: Exec ClickDW.internal.pTransfer @SourceDataImportID=X, @TranAction="R" (to be done)
	internal.pTransferEnvironment (to be done)