There are 3 possible solutions to this approach related to processing TTC transaction files for SDFA. i have not specified any particular way of data transfer type between SDFA database and data synch server which is to be identified and up for discussion. Unknown area is marked in red as "How transaction data is inserted?"
HSI project team will be working with Corporate internet security, corporate infrastructure and TTC to automate transfer of files between TTC and city's network. These 4 files are currently downloaded on monthly basis from TTC's sftp site. Out of 4 files only transfer file data will will be passed to Salesforce using existing REST API integration. Transfer file contains info related to old presto card number and new presto card number. Rest of 3 transaction reports are for SFDA's analytics purpose and its being hosted into SQL server database. In order to get these 3 transaction files into SDFA database there are 3 possible ways:
1) HSI IT team helps to create XML to JSON or CSV file format. The converted file by HSI is inserted either manually or automated process developed by SDFA database admin.
2) Once the XML file is in Network access storage then SDFA database admin has developed either automated or manual way to write these files data to their database tables.
3) HSI dev team develops code to read XML file and perform insert to SDFA database using JDBC connection. This function though requires extensive testing around file reading and database operation performance. This can involve quite much development effort as well.
If there is possible way to schedule specific file format file upload in target database without JDBC that would be the ideal.