Implementing a Data Warehouse with Microsoft SQL Server 2012: Dynamic Packages

Exam objectives in this chapter:

• Extract and Transform Data

• Define connection managers.

• Load Data

• Implement package logic by using SSIS variables and parameters.

When you are developing Microsoft SQL Server Integration Services (SSIS) packages, it is a good practice to make each task or transformation as dynamic as possible. This enables you to move your packages from one environment to another (for example, from development to a test environment, and then to a production environment) without opening and changing the package. You can also configure your packages to set different properties at run time.

To do this, you can use the new features in SQL Server 2012, such as parameters and project-level connection managers, or you can use the package configurations that were first made available in the package deployment model in earlier versions of SQL Server. This chapter discusses both possibilities for designing and configuring your package to dynamically set values at run time. Using dynamic packages eliminates the need to make changes as you move from one environment to the other or to open the project when you want to run your packages using different property or variable values.

Lessons in this chapter:

• Lesson 1: Package-Level and Project-Level Connection Managers and Parameters

• Lesson 2: Package Configurations

Before You Begin

To complete this chapter, you must have:

• Basic knowledge of SQL Server 2012 SISS control flow and data flow features and components.

• Experience working with SQL Server 2012 Management Studio (SSMS).

• Experience working with SQL Server Data Tools (SSDT) or SQL Server Business Intelligence Development Studio (BIDS).

• An understanding of the basics of file copying and security.

• Knowledge of system environment variables.