Database development The bulk processing interface between Oracle and MySQL ssis+ stored procedure implementation

The company currently uses a different database for different business systems, involving Oracle, MySQL, and SQL Server. While some of the core business is in MySQL, it is often important to insert data from Oracle and SQL Server into MySQL.

This part starts with the real-time interface, which is implemented in Java, but with the growing business logic in MySQL, the growth of the amount of miso in the data, the response to the real-time interface is sometimes problematic.    So we decided to use the bulk processing interface to pass data once every 2 hours. Less nonsense, I simplified one of the interfaces, drawing the implementation of the flowchart. I directly see that I am using the ssis+ stored procedure to implement. SSIS is used only to pass data, and the logic is put into the stored procedure.    This is to take into account: the logic to insert or update the data to MySQL complex, SSIS is not ripe, or the stored procedure is reliable, and a lot of data and contracts, money-related, with stored procedures log logs convenient, but also easy to troubleshoot errors. Now in retrospect I just got this project, I, mess, now look back only to feel a piece of cake. Sincerity is: the use of tools do not fear, always use will be, the key need to train of thought, clear-minded is the kingly way.

Realize the idea to see the diagram, stored procedures and the use of SSIS is not in the scope of this article, after the desire to write.

(Blue is the existing business table, Green is the interface new to transfer data and record history of the table)

Database development The bulk processing interface between Oracle and MySQL ssis+ stored procedure implementation