Certica Products

How to create Data Uploads in the Data Gateway Agent

Updated Mar 24, 2021

Instruction starts at 5:50 mark

https://certicasolutions.sharepoint.com/ClientServices/Certify/Shared%20Documents/Forms/AllItems.aspx?id=%2FClientServices%2FCertify%2FShared%20Documents%2FProvisioning%2FCRL%2FCRL%20Acquisition%20Provisioning%2Emp4&parent=%2FClientServices%2FCertify%2FShared%20Documents%2FProvisioning%2FCRL&p=true&originalPath=aHR0cHM6Ly9jZXJ0aWNhc29sdXRpb25zLnNoYXJlcG9pbnQuY29tLzp2Oi9nL0NsaWVudFNlcnZpY2VzL0NlcnRpZnkvRWVkV1BlYl9Xa3REdVRGZ3dsTDVaUFlCZkZNcW5qdHhMNmpBR3RYQ1NreFkzdz9ydGltZT1tcy1WS0EzUTEwZw 

Log into a Jumpbox and then:

For CA RDP to CPCAEPCAD-SQL01

For TX/AO RDP to  CPCAEPCSD-SQL01

For CEE RDP to  CPCEEPCEC-SQL01

Open SQL Server Management Studio and connect to CPCAEPCAD-SQL01

Step 1: Find new acquisition ID using variables from newly-created acquisition

use DATA_GATEWAY

go

select * 

from ACQUISITION_DATA_SOURCES 

where data_source_name = 'Aeries' 

    and src_db_jdbc_url = 'jdbc:sqlserver://EGL-AWS-SQLA01.asp.aeries.net:1433;DatabaseName=DST19000SLZUSD'

Step 2: Find data_uploads to copy using variable from known good acquisition

select * 

from DATA_UPLOADS 

where DEST_TABLE like '0607230%'  

Step 3: Copy over the Data Uploads from the known-good acquisition to the new acquisition. 

NOTE this is running as a BEGIN TRAN statement so you'll have to either COMMIT or ROLLBACK to not lock up the database.

declare @vNCESLEAID varchar(100) = 'XXXXXXX', -- this is the new district's NCES

            @iNew_Acq_Data_Source_ID int = XXX, -- this is the ID from Step 1

            @iCopied_Acq_Data_Source_ID int = XXX-- this is the ID from Step 2

begin tran

insert into DATA_UPLOADS (ACQUISITION_DATA_SOURCE_ID, DEST_SCHEMA, DEST_TABLE, SQL_STATEMENT_ID, DATE_CREATED, DATE_LAST_MODIFIED, LAST_MODIFIED_BY, PRECOMPRESSED_DATA_BUFFER_SIZE, ENABLED)

select @iNew_Acq_Data_Source_ID, DEST_SCHEMA, @vNCESLEAID + RIGHT(Dest_Table,(Len(Dest_table)-7)), SQL_STATEMENT_ID, GETDATE(),GETDATE(), LAST_MODIFIED_BY, PRECOMPRESSED_DATA_BUFFER_SIZE, ENABLED

from DATA_UPLOADS 

where ACQUISITION_DATA_SOURCE_ID = @iCopied_Acq_Data_Source_ID

Step 4: Check that the Data Uploads are in the new acquisition

declare @vNCESLEAID varchar(100) = '0601770', -- this is the new district's NCES

        @iNew_Acq_Data_Source_ID int = 455, -- this is the ID from Step 1

        @iCopied_Acq_Data_Source_ID int = 448 -- this is the ID from Step 2

select * 

from DATA_UPLOADS 

where ACQUISITION_DATA_SOURCE_ID = @iNew_Acq_Data_Source_ID

Step 5: If looks good then run COMMIT. If it does not look good then run ROLLBACK. 

--commit

--rollback 

Previous Article How to create a Feature Request:Story
Next Article How to Decommission a district