Import of HUD CSV files (in Train if the Conversion Environment is not available)

Steps to take before doing any HUD CSV Import

Location: Determine the environment you will import the data into. The below steps will be done in the Training environment first, then do it again for Conversion (if one exists) 

Note: Scripting the data if preferred is faster for you. 

1. Create all the setup data
2. Create a new endpoint: Home workspace → Global Administration → System Integration → Integration End Points → Add New → enter the name, select “Sends Data to this System” for Ent Point Type, choose the main organization for a customer, enter 1 for Database ID, enter 1 for Client Information Release No, keep default values for Restriction fields, all the other fields will stay blank/as is. 
3. Action button for new endpoint created, you need to fill out all the mappings for all the action button links, except for the “DEPRECATED RHY Referal Category”. There may be some that don’t have any values or no mappings available to match to, but that is fine. This requires all the service codes for Setup Data to be in place (refer to instructions Import Set-up Data from CSVstep 1) .
4. Add a new drop-down list value of “Health Insurance obtained through COBRA” for list #2557. I did a sync so this is now in Conversion.
5. Compliance Crosswalks: Home workspace → Global Adminisration → System Administration → Compliance Management → Compliance Crosswalks → complete all the crosswalks for the HMIS 2022 compliance Standard. If there is a checkbox for “Use this value for import”, make sure it is checked. But if a Standard Value is used more than once, you must only choose one for the import. Match the standard value to the item as best you can. If there are no Standard Value list options, you don’t need to do anything.
6. If you are using a conversion environment: Copy all data from the training environment into conversion (see attachments “IntegrationRemoteKey_InsertIntoProdFromConversion.sql” and “StandardValueMapping_InsertIntoProdFromConversion.sql”)

Run the HUD CSV Import workflow:

1. Once all the above steps are completed in train and/or conversion, then go into the migration environment (conversion if you have one)
   1. Update form: #4893 Copy and apply workgroup override for ALL workgroups, including all baseline workgroups.
2. Customize the following:
   1. Append to Join Clause for Organization table join: “AND IntegrationRemoteKey.TableName = ‘osOrganization’ AND IntegrationRemoteKey.ColumnName = ‘OrgID’
3. Update workflow: #2314 Copy and apply workgroup override for ALL workgroups, including all baseline workgroups.

Customize the following:

Initialize step - change the Set Variable action, removing the @ for the Variable name @TaskID@; so it is like this:

Get UserOrgID step - move the second action to the first in list so the @EndPointID@ variable is set first so then the @OrgList@ can be set using the endpointID just set. Here is the correct order:

Home workspace → System Integration → Import → HUD CSV Import → follow all steps of workflow filling out all data as much as possible. On CSV Import screen, you do not need to fill out all the variables.

When we get to the “Review the Log” step in the workflow, I suspect all the staging tables are populated: (schema of “integration”) integration.HUDCSV2022_Assessment, integration.HUDCSV2022_Client, etc.

NOTE:  if this (define this) is true, once you get to the last step in the workflow (Import the Data) choose the option to reject the data.

You will need to manually run the procedure in schema “integration”: integration.HMISCSV2022_Import_Validate supplying the parameters of EndPointID, TaskID, and UserID.

EndPointID = new endpoint you created (always use the same endpoint or duplicate data will be created)

TaskID = get the TaskID that was created during the workflow (AsynchronousTask.TaskID where TaskName = ‘Import CSV Data’)

Once the procedure is completed without error, review the information and let the PM and IE/QA know what the invalid records data are saying. 

Run the following query for a distinct list of the invalid information: “SELECT DISTINCT RecordType, Reason FROM IntegrationInvalidRecord”. Nothing more can be done until the customer has provided us updated files or direction on action to take to get errors resolved (for example, update the null/blank values to “data not collected” value)

if no records exist in the table, then run all the procedures in the correct order to migrate data into CT tables. “SELECT * FROM CSVStoredProcedures WHERE integrationID = 151 ORDER BY ExecutionOrder” This will provide you the order the procedure need to run. You must do it in this order. If errors exist, they must be resolved and may require transaction data to be removed and then starting the procedures again from the beginning. 

NOTE: Just a heads up that the one procedure I have errors the most (and runs forever - hours and hours is DisabilitiesEval procedure. We highly recommend disabling the trigger for the AssessmentEval table, as this is the cause of forever executing. “ALTER TABLE AssessmentEval DISABLE TRIGGER ALL;” Once the import procedure is completed, I then enable the trigger “ALTER TABLE AssessmentEval ENABLE TRIGGER ALL;” and just run Update AssessmentEval SET AssessmentID = AssessmentID FROM AssessmentEval so then that will get trigger to run.)

if you have records that need to be corrected, the client may need to help define the values. Once you get the fixes in place, you should start the import process over again, so getting a new task ID and then verifying no other records are invalid.

Inform the team the import has been completed. Ready for QA.

I have attached all the SQL queries I use to help me in the import process.