WeDid: CSV upload to SQL Database
May 29, 2014 /
Posted in We Did
Challenges
Our Financial Services client required daily data uploads of investment related data into their Investment Management System. The data arrived in CSV files that were transferred via FTP each evening and loaded into the MSSQL database. The integration was needed within days with minimum requirements available. The solution needed to be integrated into a larger process and provide detailed error reports for any data issues found in the incoming data files.
Solution:
| Purpose | Develop a rapid integration component to read CSV, perform data validation and upload to a database. |
| Applications | Microsoft SQL Server |
| Versions | Microsoft SQL Server 2003 |
| Tool | Talend Open Studio |
| Information | Financial data |
| Data Formats | CSV File format |
| Volumes | ~1000 transactions per day |
| Process | The financial data is retrieved from a local or network drive and processed. The validated data is then loaded into the database using stored procedures. The integration has a configurable error threshold after which a database roll-back will be initiated. The calling process is notified of any errors, which are logged in detail in a database table. |
| Schedule | Daily batch processing |
| Error Handling | Custom built logic to handle field and record level validation and update the log table with precise error messages. |
| Complexity | Medium |