Common SSIS Applications

One common use for SSIS is to move data from one data source to another. The reasons for moving data are too numerous to count. Some common business reasons for using SSIS include migrating business data from one application to another, extracting data for distribution to external entities, integrating data from external entities, creating sample test data sources for development environments, and extracting and loading data into business intelligence (BI) application systems.

SSIS works extremely well in SQL Server environments, but it can also be used with many non-SQL Server database file types and many of the other database management systems deployed within your Information Technology (IT) environment. SSIS has the ability to read data from other Microsoft products, such as Microsoft Office Excel spreadsheets, as well as text, Extensible Markup Language (XML), and other flat-file types.

One common IT demand in the past few decades has been the need to provide business information to a wider audience within an organization. Business intelligence is a relatively new term, but it is certainly not a new concept. The idea is simply to use information already available in your company to help decision makers across the company make decisions better and faster. BI systems can be custom developed or deployed through a variety of packaged reporting and analytic tools. The common component among the various BI systems is the underlying data that drives the information and analysis.

When you need to provide fast-response BI applications for many purposes throughout a large organization, the data that drives such systems most often comes from multiple sources. SSIS provides you with the ability to design and execute data integration operations as simple as moving data between application databases or as complex as consolidating large volumes of data from multiple data sources in different formats, while at the same time applying rules to standardize, modify, and cleanse data content prior to loading into BI data warehouses designed for reporting and analytical applications. You will learn more about data warehouse application characteristics and the role of SSIS within BI and data warehouse applications in Chapter 12, "Data Warehouse Concepts," and Chapter 13, "Populating Data Warehouse Structures," later in this book.

Even if you're not responsible for creating and maintaining a data warehouse, a reporting operational data store, OLAP cubes, or other BI applications, you'll find the features in SSIS quite useful for routine database administrative tasks and many other activities in which you need to move, transform, and load data in any form.