• Intuitive UI with capabilities to collect and merge data from various sources makes it easy to build projects. 
• Being part of a large Microsoft community SSIS has extensive documentation and support on the world wide web.   
• A developer can write custom scripts and even reuse scripts between projects to implement requirements specific to his business needs.
• Seamless and tight integration with the Microsoft family of products.  
• Cost-effective.
• Robust error handling allows customised error configuration to prevent and identify breakages.  
• It provides BI output with a data transformation procedure.  
• Without creating explicit trust relationships between those systems, SSIS can query, filter, and transfer data between databases on separate servers.  
• SSIS is a full-featured product with many transformation features that allow for sorting, lookups, using a variety of data sources and targets. 

SSIS PACKAGE GENERATION : THE CHALLENGE

“Package” is the unit of work in an SSIS project. A single package in an Integration Services Project is supposed to do one single task, such as : load data into a fact table in the data warehouse, send data from one server to another or make a database backup; basically, any tasks that you want to do with data.  

Many organizations have industry standard Data Warehouses (DWH), populated by SSIS packages. In a typical situation, SSIS packages read data from operational databases, change the data, and then write the data to the DWH.  

Creating SSIS packages demands significant attention to details through adherence to best practices & standards and developer expertise. Therefore, it can be error-prone and time-consuming. As a best practice, a package needs to be written for each task. The package must be rewritten if there is a change in source schema or metadata.   

Developers often need to repeat steps in creating packages. As a high code solution, fixing one thing tends to break others. Inconsistent application of best practices can lead to inefficiencies due to poorly designed source queries, inefficient transformations, slow destination flows or incorrect package configurations.  

The biggest challenge is that environments change even when the code is perfect. The data source can be added or altered, and so can the data formats and their business intents.   

This could lead organizations to face challenges in:  

• Getting Load performance and Managing Data  
• Ensuring Data Security and Compliance  
• Require additional development efforts, increasing the total cost of ownership  
• Enforcing consistent coding best practices to ensure maintainability and performance  

Thus, SSIS upkeep could demand entire refactoring and testing of the SSIS solution causing it to be heavy on support and maintenance costs.   

From a business viewpoint, creating and maintaining SSIS packages is problematic for many companies without large development and coding staff. Adhering to timelines, budgets, and even start-up or maintenance costs will be high. In addition to the above, inheritance of code across vendors or even between developers tends to increase costs disproportionately, owing to quality issues.   

Though ETL with SSIS is a powerful tool, the learning curve for new developers in an inheritance scenario is steep. Custom business scenarios require coding in C# or VB & require experienced developers.