WeDid: CSV upload to SQL Database


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.




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