Using iTKO LISA

SSIS package testing with iTKO LISA

What are SSIS packages?

SSIS stands for SQL server Integration Services. SSIS packages can be created in Business Intelligence Development Studio (BIDS). These can be used to merge data from heterogeneous data sources into SQL server. They can be used to populate data warehouses, to clean and standardize data, and to automate administrative tasks. SQL Server Integration Services (SSIS) is a component of Microsoft SQL server 2005. Integration Services provides a platform to build data integration and workflow applications. The primary use for SSIS is data warehousing as the product features a fast and flexible tool for data Extraction, Transformation, and Loading (ETL).

Brief about the Project

A children's organisation working with communities in 48 developing countries and provides programs to over 1.5 million children and their families. Child sponsorship is there main source of funding. They have a staff of over 6000 worldwide and further volunteer force in excess of 50,000 and have regional and field offices. Each regional office operates independently using different fundraising methods within each country. New system was been developed which was been location at their Head Office which was centralized database and all the activities of regional and field offices were been routed through Head Office. Due to its diverse location and vast transaction it was impossible to implement the applications in one go, so the application was rollout in phases. It was impossible to retire the legacy system so to maintain the consistency of the data between and new application and the old legacy system SSIS packages were used.

Testing SSIS packages:

The structure of the database, their were tables from legacy system (around 200+ Tables) where the source data was available, the destination tables (around 300+ Tables) where the data will be inserted, and the audit tables or holding tables which holds the data if there are any invalid records. There were more than 500+ business validation and have more than 20 file formats through which these transactions were supposed to happen. The test conditions were right from the file naming convention, length for the file, till data in the file.

Challenges in Testing SSIS package:

To validate all these business validations, there were many challenges for testing team.

Example:

File name: XXX-YYY-CCYYMMDD-001.NNN

Where XXX will be the details of what type of transaction is there in the file, YYY will be the location code, CCYYMMDD the date format, 001 was sequential number which would be incremented in each system generated file, and NNN will be region code.

File length: There was different specified length of the file from 160 characters to 1022 characters.

Data validation: NNNnnnnnnn this type of code were been used for the children. The expected validation were NNN should be the region code, which should be same as of the file name NNN, remaining 6 characters should be valid child code which are related to the region code.

Apart from this the start and end position of each field, the different validation such as space, special characters and many other validate were involved.

When the test team was been asked to test SSIS packages, they were hardly aware of how to test it. No specific tool was available to test. And due to the huge scope of testing with limited resource it was difficult for the test team to test it manually, as to validate 500+ business conditions was very difficult and also to create the scenarios to validate all the business validations. Many brain storming session were held, people from the team were searching on the net for tool which can help them with testing and which are easy to use. There was a team in the organisation, which was working on testing web services. They were using a tool named iTKO LISA which was specifically used for SOA projects. They were been approached for any suggestion from them. All the constraints in testing were been discussed. The team suggested about the feature of LISA to directly communicate with all type database. No client software was required by the tool to communicate with any database, but just the related .jar files. A Proof of Concept was done which was a great success and later the tool iTKO LISA was used for Test Data creation, and validating the source, target and audit tables.

Solution: itKO LISA

LISA stands for Learn, Invoke, Simulate and Analyze. Used for functional, integration, business process testing of SOA projects and is a no-code software testing solution.

The test steps which are commonly used for SSIS packages testing are:

For Test Data creation:

Parse Text as Response which is used for test data creating, The data set type Read rows from Excel file was used for sample data which was used to create the file which was placed at the shared location where the scheduler was running. The package when executed used to process the file as per the business conditions. To ensure all the business validation:

LISA step SQL Database Execution (JDBC) step was used. Simple select * queries were used. For business validation different types of Assertions were used, which used to validate the error codes against the codes provided in the sample excel file.