Introduction

As technology evolves, it is always necessary to integrate data between different systems. The integration component is increasingly gaining importance, especially the component responsible for data quality as well as the cleaning rules applied between source and destination databases. Different vendors have their own integration tools and components, and Microsoft with its SSIS tool is recognized as one of the leaders in this field.

SSIS can be used to perform a broad range of data integration tasks, and the most common scenarios are applied to Data Warehousing. The known term associated with Data Warehousing is the Extract Transform and Load (ETL) that is responsible for the extraction of data from several sources, their cleansing, customization, and loading into a central repository (for example, to a Data Warehouse, Data Mart, Hub, and so on). SSIS is also used in other scenarios, for example data migration and data consolidation. Data Migration is the one-time movement of data between databases and computer systems, and is needed when changes occur or when we upgrade our systems. Data Consolidation combines and integrates data from disparate systems and assumes high importance in a business environment with increasing acquisitions and mergers. The following diagram adapted from TDWI (www.tdwi.org) helps clarify the different scenarios where SSIS could be used:

New business challenges are driving organizations to adopt data integration projects. Some of these challenges are:

• Increasing demand for real-time information reporting and analysis

• Large volumes of data spread along the entire organization

• The need to comply with regulations, which often require to continuously track all changes to data and not just the net result of those changes

Although SSIS is an amazing tool for data integration, the same work can be done manually in almost all cases. As you can imagine, performing data integration tasks manually could be hard to maintain in terms of code, hard to scale properly, and would require more time to implement. From our perspective, since we have SSIS, there is no real reason to do it manually. The cost of ownership is not a problem either, because SSIS is included with SQL Server licenses that most organizations have already acquired.

In this chapter you will learn how to work with SSIS, how to create packages for data transfer, and you'll perform some simple operations with SSIS Package. At the end, we will highlight several improvements which are included in this new version.

Note

As we will cover many recipes in this book, it is advisable to have Adventure Works SQL 2012 sample database installed.