ISCC Process - Projects: Run three projects

• Each project will be stored in your currently active folder: Bethany_SpectralCoreprojects
  • 1_PDStoSQL_POL_Bethany: Copies the PDS Paradox files to the SQL database set up for this conversion.
  • 2_SQLSourceTOSQLFS_POL_Bethany: Updates the PSFS Tables with the PDS data that was copied here in project 1.
  • 3_SQLFStoAccessDesktop: Use SQL Server to run Queries to update data then copy? to Access.

Project 1   |  Project 2a   |   Project 2b   |   Finish (Project 3)

Project 1

Open Spectral Core and copy the PDS Paradox file to the SQL PSFS tables using the following steps:

Note: this will move the PDS Paradox data to the SQL PSFS tables.

1. Open Spectral Core tool
   
2. Open Project.
   
   
   • Browse to the customer folder: PDS_Bethany_test → Conversion Package → BaselineSpectralCore_ADMINBOX
     • customer folder name would follow this format
       
   • Choose the first item 1_PDSTOSQL- straight insert from PDS file to SQL database
     • Select and click open
       
       
       
   • NOW Save AS, in Conversion Package, create a new folder (the one in this example is Bethany_SpectralCoreprojects) and save the project named 1_PDStoSQL_POL_Bethany;     This creates 'my own project' and we don't use the baseline box anymore for my project. *never replace the baseline folders but create my own and update that.
     
     1_PDStoSQL_POL_Bethany

1.  
2. Click the Paradox option. (This is to link the Paradox option to the customer's PDS Paradox file.)
   
3. Click the Browse folder button and browse to our customer's PDS Paradox file in my own "source" folder then Connect: Then Save.
   
   • Look in the H drive into folder Conversion_In_Process. This points to the correct database.
4. Now click SQL server. (This is to link to the new PSFS database that Zel created for this conversion)
   
   • Click SQL server and browse to review server databases; select the one that zel created for the conversion specialist.
     
   • Always click Connect. Then they are connected.
     
   • The Source tables will be processed. 
     
5. NOW Save again (in Bethany_SpectralCoreprojects under name 1_PDS_to_SQL_Bethany)
6. Copy the database: copying the data from the PDS file into my review database: When you click Copy your database, it takes the Paradox files and copies them into the SQL Server database creating PDS tables into the PSFS database.
   
   
7. May get some warnings.
   • • Can always review the last 'event' if you leave and come back
     • At this point, if you get warnings, contact Zel for resolution.
       
       
   • If you close out of Spectral Core, when you re-open Spectral Core, you will see your project in Recent Projects
     • can review the last run if you have questions for Zel, etc. 
8. Can look at both source and target tables
   
9. Save then click Close (this project)

Top  |  Project 2b

P2 Use Spectral Core to update PSFS tables with the PDS tables

Project 2; Will save it as: 2_SQLSourceTOSQLFS_POL_Bethany

1. Go to BaselineSpectralCore_Adminbox and highlight 2_SQLSourcetoSQLFSAdminBOX.fcpx, then click open.
   • Right away, Save AS  "2_SQLSourceTOSQLFS_POL_Bethany"
     
     
     
   • Source database connection SQL Server
     • Server: mbpsappconv01.parishsoft.mbhosting.com\REVIEW
     • Database: *Customer's folder (PDS_CustomerName_CustomerPOL) PDS_Bethany_Test
   • Connect
   • Save project (overwriting 2_SQLSourceTOSQLFS_POL_Bethany)
     
   • Target database connection SQL Server
     • Server: mbpsappconv01.parishsoft.mbhosting.com\REVIEW
     • Database: *Customer's folder (PDS_CustomerName_CustomerPOL) PDS_Bethany_Test
   • Click Connect
   • Save project
     
   • click show selected only; click save;
     
     • Click Save
     • Click Copy your database; end of Spectral Core;
     • Click Close
       warnings: contact Zel for warnings
       

Top  |  Project 2b

Project 2b Open project in SQL Server and run queries to update the PSFS tables with the PDS data

1. Go into SQL Server
2. Connect to the review screen: mbpsappconv01.parishsoft.mbhosting.com\REVIEW
   
3. Find the Customer Folder PDS_customer-name_POL_ConverterInit and double click. (PDS_Bethany_TEST); Use the + Databases to find the folder.
   • Make sure the folder is in the dropdown at the top.
     
4. Get and run the queries in order:
   1. Click New Query* just click on the written queries and it will open a new query
      
   2. Click the Open  File.
      
   3. Select the down-arrow and browse for the customer folder: 
      PDS_Bethany_Test (PDS_####_bms) → Conversion Package → SQL Scripts → 1_Census → ...
      
   4. Set the Commit Bit to zero so it will not update right away until I am ready
      BEGIN TRAN
      DECLARE @COMMIT BIT = 0;
      
      
      • 1_ReplaceLastNameinMastermemberindexwithcorrectname.sql
      • 2_
      • ...
      • 17_Tbladdressfromfamand..
   5. Select the first query, click Open, and it will populate the Query page
      
   6. Then go back to #3 but open the next folder and do the same thing: At the end run the 100_LASTscript_PopulateSourceandownerto
      

Should always have your own scripts (your copy) so you can update the script according to your needs but you leave the main ones alone.

after all done use final package which is #3 (writes to access)

Top   |   Finish

Final Process: P3

Need an empty blank database to move everything into Access 

Then run the Project #3 desktop to be in a safe place.

Then ready to schedule conversion.

mbpsappconv01.parishsoft.mbhosting.com\REVIEW

April 17, 2023 16:58