UTI Uploads

Uploading Leads from LeadsUTI ftp folder into the database then emailing the reports

Narrative

1. UTI sends one file which we modify. The resulting modified file is imported into Housing then reported on and sent to the Housing department.

Save Attachments

1. We currently get the file 2 ways. They send an email with a secure web login that allows us to download the file. They are also placing the file on our secure ftp site. (The messages typically arrive around 11:00 am on Mondays)
2. I now just edit the file directly in the uti ftp folder.

PATH: \\FRED\ftproot\UTI REPORT NAME: UTI_CHS-DATE.txt.gpg

Prepping the Report

1. Decrypt the uti ftp’d file.
   1. I use Gpg4win to decrypt the file. The passphrase is in KeePass Master DB in the Encryption folder.
2. You should now have a .txt file. Open this with excel. Make the delimiter a pipe (|). Now it should look normal in excel.
3. Rename the tab at the bottom of the spreadsheet to Sheet1
4. Cut a row that contains a ‘New Start Date’ then insert the cut row to be the first row of data. This step is necessary for the import to recognize and grab the ‘New Start Date’ data.
5. At this point I take not of the total number of lines in the file. I double check this is the number of lines that get imported.
6. Save the file as ‘UTIReport4Import.xls’.

Import Reports

1. Remote to tlsql.
2. Open the folder: C:\SSISpkgsdata
3. Date the existing ‘UTIReport4Import.xls file and move it to the ‘Old Files’ folder.
4. Open another folder to the ftp file you previously edited then copy the newly edited file to the SSISpkgsdata folder.
5. Double click ‘UTIPackage.dtsx file. (This is a new dtsx file that handles the new UTI data and file formate.). Select ‘Execute’ to run it. When this finishes you can scroll to the bottom and check the number of records imported.

Notes:
This puts the data into a holding table:  Table_Sch_FSList_LeadAllInOne
You will need proper ODBC connectivity (SQLTLDataLink).

1. Open MSSMS.
2. Query the new dataset. This should return the correct number of rows.
   1. SELECT * FROM Table_Sch_FSList_LeadAllInOne WHERE (aio_CreatedDate > ‘6/11/2018’)
3. Check that they did not send leads from campus id 53 (UTI-Bloomfield). We do not services this campus. We need to remove these if they exist because they do not allow the import to finish.
   1. SELECT * FROM Table_Sch_FSList_LeadAllInOne WHERE (aio_CreatedDate > ‘6/11/2018′) AND (campus_id = ’53’)
   2. DELETE FROM Table_Sch_FSList_LeadAllInOne WHERE (aio_CreatedDate > ‘6/11/2018′) AND (campus_id = ’53’)
4. Execute 2 Stored Procedures
   1. Open SQL Server Management Studio
      1. Databases -> DBTL -> Programmability -> Stored Procedures -> right-click and select ‘Execute Stored Procedure’
   2. dbo.zsp_UTILeads_E (This may take a minute or two) (Recently I get an error when they send leads from schools we do not service. I search for and delete these leads (deleter from Table_Sch_FSList_LeadAllInOne). I then re-run the dbo.zsp_UTILeads_E procedure.).
   3. dbo.zsp_SchRpt_C

Send Lead Reports

1. On your workstation run the following app:

H:\CHSApps\Michele Gaw Files\Leads\MyTLSQLDataLeads.accdb

1. Select the ‘Update Linked Tables’ button -> select all -> ok -> login with Sage credentials -> ok.
2. Select the ‘Open Withdrawl Form’ button -> ‘E-mail List’ -> ok

To: Rachelle Chavez; Molly Marsh; Nia Lassalle
cc: Amanda Wiley; Kimberly Alexander
Subject : UTI Withdrawals for - currentdate
Message: Here are the UTI withdrawals from last week.  Please note that these have NOT been processed or flagged in the system.

1. Select the ‘Open Cancels/Postponements Form’ -> ‘E-mail List’ -> ok

To: Kelly Piacenti; Megan Holman; Paige Hanson; Elizabeth Bolinger 
cc: Kimberly Alexander; Amanda Wiley
Subject: UTI Cancels and Postponements – currentdate
Message: Here are the UTI cancels and postponements. NOTE: The Leads are uploaded.

1. Done! Close everything…..