A case study on how the client organization moved from manually built data transfer setups to a centralized SSIS automation workflow.
The Client
The client is an established global investment management organization focused on managing alternative investment strategies across credit, real estate, and other specialized asset classes. With decades of experience identifying complex market opportunities, the firm manages a broad portfolio of investment products for institutional investors worldwide. Supported by a vast team of operations professionals and a presence in key financial markets, the organization emphasizes disciplined research, risk management, and long-term value creation for its investors.
Challenges Faced by the Client
The client regularly exchanges sensitive financial data, including portfolio summaries, trade records, and end-of-day reports, with their end clients. These files are moved between internal databases and secure SFTP folders daily, weekly, or monthly, depending on client agreements. As the number of end clients increased, the way these transfers were handled began to put pressure on the technical team:
- Every new client meant building a new data transfer setup: When a new client needed information assets, developers had to create a fresh data transfer workflow from scratch, with necessary configurations.
- The same type of data process was written multiple times: Since the data transfer workflows were built separately for each client, similar scripts and configurations were scattered across multiple places.
- Monitoring business applications required separate setups: The client runs multiple internal apps and services that provide investment-related data. Checking whether those services were running, pulling their output files, and loading them into databases required individual configurations instead of a unified approach.
- Data between development, testing, and live systems was done manually: When technical teams needed to copy records from one environment to another for testing or validation, it required a separate setup each time, adding extra effort during release cycles.
Solutions
To avoid rebuilding similar transfer processes for every new client/requirement, our specialists at UBTI designed a single reusable automation structure using SQL Server Integration Services (SSIS). Therefore, instead of creating separate workflows for each client or scenario, we built a single configurable automation package that can act like a digital workflow engine as follows:
- Database records are converted into secure client-ready files: The SSIS package extracts required information from SQL Server tables using predefined queries. It converts the results into formats (CSV, Excel, text, or XML) and places the file in the client’s SFTP folder. The configuration also specifies where the file should be stored and which users should receive a notification when it is generated.
- Files uploaded by end-clients are automatically collected and processed: The system regularly checks SFTP locations for newly uploaded files from external partners or end clients. When a file appears, our solution automatically downloads it, reads its contents, and loads the data into the appropriate database tables. The original files are then stored in organized folders or archived for future reference.
- The system retrieves required information from internal apps on a schedule: In some cases, the files delivered to end-clients are first generated by internal business apps and are made available via secure web links. The automation package accesses those links at scheduled times, downloads the available data or files, and stores the information in the database. If the downloaded files are compressed, the system auto-extracts them before processing.
- Supporting database procedures, prepare data for processing: Before certain files can be transferred or shared, the required data must first be prepared. This preparation sometimes involves running internal programs or executing database procedures that generate reports or update records. The SSIS automation package performs these steps automatically, ensuring that the correct data is prepared before the next stage of processing.
- Data is transferred between development, testing, and production environments: When teams need to copy records between environments for testing, the package performs the transfer by reading the source table and inserting the data into the required target table. This ensures that internal systems remain aligned throughout development.
- Processed files are stored in organized locations and shared via notifications: Whenever files are created or received, the system stores them in predefined directories and keeps backup copies where required. It also sends automated email messages based on configured rules (such as sharing reports, sending alerts, or confirming that files have been delivered to external partners).
Solutions Benefits
With our SSIS-ETL package, the client now operates its daily data exchange and processing activities through a structured workflow rather than separate manual setups. Routine tasks that previously required developer intervention are now handled through a single coordinated process, resulting in:
- Faster onboarding of new client requirements: New reporting or data transfer needs can be configured quickly within the existing setup instead of building new workflows from the beginning.
- Consistent and reliable data movement: Standardized data processing ensures that files are generated, transferred, and loaded in the same way every time, reducing inconsistencies across clients and processes.
- Major reduction in manual operational effort: Automating routine data transfers and reporting activities reduced developer effort required to run and maintain these processes by nearly 90%.
- More efficient use of system storage: Avoiding duplicate packages and scripts helps prevent unnecessary storage usage while keeping operational processes cleaner and easier to manage.
- Reduced duplication of technical setups: Using a single reusable SSIS-ETL automation package prevents the creation of multiple similar workflows, making the overall system easier to maintain and update as the business scales.
- Lower overall process costs: Moving from manually managed workflows to a reusable SSIS automation package reduces the total process cost by 50-60% (approx.).
Conclusion
The client needed a reliable way to manage daily file transfers from end clients across systems without having to set up new systems each time. As the client’s requirements increased, manually creating workflows began consuming developer time and made routine operations harder to manage consistently. Our experts at UBTI addressed this by implementing a reusable SSIS framework that unified file generation, data retrieval, secure transfers, and operational monitoring into a single configurable workflow. Today, these processes run through a coordinated system managing data preparation, file handling, and sensitive data movement. UB Technology Innovations, Inc. continues to support asset management firms with stable and highly efficient data automation and client reporting systems.
About Us
UB Technology Innovations, Inc. (UBTI) is a leading global technology solution provider with over 3 decades of experience across all industries, specializing in Capital Markets, Logistics, and Healthcare. We are the preferred Microsoft Solutions Partner backed by a world-class team of Microsoft Certified experts with rich experience in Azure Cloud Platform and Data Analytics.
