An important part of any Business Intelligence solution is moving the data from a source environment that isn't optimized for analysis into one that is. Sometimes this process can be straightforward; at other times, you will need to make structural or formatting changes to ensure that the data has value. This is known as the ETL process.
The Extract, Transform, and Load (ETL) process can be managed through a SQL Server service known as SQL Server Integration Services, or SSIS for short. This chapter will cover the basics of SSIS in SQL Server 2008. The following topics will be covered:
A general introduction to SSIS and its features
The import and export tools used to move data around
The different options for transforming data using SSIS
Prior to SSIS in SQL Server 2005, SQL Server included a lightweight ETL product known as Data Transformation Services (DTS). Although DTS was a useful tool for moving data from one location to another, it was prohibitively difficult for many administrators who lacked significant programming or scripting skills to perform complex transformations. SSIS builds on the basic principles of DTS, but expands its capabilities to include additional, easier-to-manage, features.
Integration Services is part of a suite of tools included in the Business Intelligence Development Studio. As you may have read earlier, BIDS is simply an instance of Visual Studio, which includes add-ins for designing ...