Microsoft SQL Server

Migrating from SSIS to T-SQL

Version - SQL Server 2014
Client based in – US

The Challenge

The client provided us some SSIS packages, which extracted required information from their servers having identical databases. For some servers, it was not possible to use SSIS and the client wanted to have T-SQL Queries developed, which would return the same results as those of the SSIS packages.

The Solution

This SSIS packages involved complex business logic. It was not as simple as picking up a SELECT query from the SSIS package and create a new stored procedure that would output the results of the query.

There were many derived column transformations, and we used CASE statements to achieve the desired result.

The SSIS packages used lookup transformations and filtered data. We used JOINs to achieve the results using T-SQL.

Condition based inserts and updates were managed in SSIS using conditional split. Instead of writing complex and lengthy INSERT and UPDATE statements, we found it beneficial to make use of the MERGE statement.

A couple of packages also involved PIVOT transformations. The pivoting was done on multiple columns. We choose to get the final result using a GROUP BY statement instead on applying multiple pivots on the data.

Apart from these, we also optimized several T-SQL statements originally used in the SSIS packages.

We completed the task accurately and well before time. This was a good learning curve for us.