Microsoft Business Intelligence

Generic extract package that executes stored procedurs

Version - SSIS 2013
Client based in - USA

The Challenge

Our customer basically needs to extract health care data from their databases into flat files. The data is very bulky. The customer is well versed in SQL and wishes to utilize this fact and develop an SSIS package that eliminates the need of SSIS knowledge for their employees, leverage their knowledge of SQL and make extracts possible by just writing stored procedure(s).

The Solution

We have developed a generic extract package that fulfils the requirement.

There was an evident challenge here, that the package would have no idea of the meta data of the extract result that the stored procedure would return. The meta data of the result would be different every time. So, we could not use simple input/output mappings that SSIS offers. There are third party tools available that allow handling such dynamic inputs, but the customer was not willing to use any third party tool due to licensing and other maintenance issues.

Another challenge, as the data is very bulky, was handling memory issues.

The package provides a solution that overcomes these challenges and performs the following steps,

• Execute one or more stored procedure(s)
• Write results of these stored procedure(s) to file(s)
• Archive(s) files if specified
• Send file(s) to SFTP if specified
• Log execution details in customer’s ETL Framework

The package offers flexibility to configure certain parameters. The package is configured to fetch these parameters from customer’s ETL Framework.

These parameters include,

• List of stored procedure(s) to be executed
• Server and Database on which to execute these stored procedures
• Type of file (CSV, TXT, any other) to be generated
• Format of file (add column names in extract?, add text qualifier?)
• Location where the output file(s) need to be generated
• Whether to archive file(s) and location of archiving.
• Whether to send extracted files to specified SFTP location