New SSIS 2012 Designer Features
- 9/15/2012
- The Integration Services Designer
- Configuration and Deployment
- New Tasks and Data Flow Components
- Control Flow
- Data Flow
- Scripting
- Expressions
- Summary
Configuration and Deployment
Solution Explorer Changes
The Solution Explorer window has been updated for SSIS projects in SQL Server 2012 to support the introduction of the Project Deployment Model. Two different deployment models are supported in this version of SSIS. Figure 4-8 shows a solution with two projects; the top project (named File Based) is in the Package Deployment Model, whereas the bottom project (named Project Based) is in the Project Deployment Model. New projects created in SQL Server 2012 will be in the Project Deployment Model by default. Existing projects from SQL Server 2005 and SQL Server 2008 will open in the Package Deployment Model. You can switch models by right-clicking the project name, and selecting Convert To Package Deployment Model or Convert To Project Deployment Model from the shortcut menu. The nodes that appear under a project will change depending on the active deployment model for that project.
){kind=link}
Figure 4-8 The SQL Server 2012 Solution Explorer window.
Project Parameters
The Project.params node is a file that stores any project-level parameters. This node will appear for only projects in the Project Deployment Model. Double-clicking this node opens the parameter designer (Figure 4-9).
)
Figure 4-9 The designer for project-level parameters.
Connection Managers
SQL Server 2012 allows you to create connection managers at the project level, which get shared among all packages within the project. Each shared connection manager appears as a node under the Connection Managers folder, and is stored in a separate file (with a .conmgr extension). Shared connection managers are available only when using the Project Deployment Model. Shared connection managers replace the use of Data Source (.ds) files from previous versions of the product; the Data Sources folder is shown only when using the Package Deployment Model.
Parameter Tab
Clicking the Parameters tab opens the parameter designer for the current package. This designer allows you to view, create, and edit the parameters exposed by the current package. Parameters function a lot like read-only variables, and they have many of the same properties. Figure 4-10 shows the package parameter designer, with three parameters defined.
)
Figure 4-10 The package parameter designer.
Visual Studio Configurations
Visual Studio allows a project to contain multiple configurations. In most types of Visual Studio projects, configurations are used to define different build properties (Debug vs. Release), or settings that affect the way the application runs on a certain platform. SSIS supports the use of Visual Studio configurations in SQL Server 2012, allowing you to externalize your parameter values for different environments. SSIS projects contain a single Development configuration by default. Configurations can be managed by right-clicking the project name in Solution Explorer, selecting Properties, and clicking Configuration Manager.
You can externalize a parameter value by clicking the Add Parameter to Configuration button in the parameter designer (Figure 4-11), which opens the Manage Parameter Values dialog box (Figure 4-12). When this is launched from the package parameter designer, you will be able to externalize parameters declared at the package level. Launching the user interface from the project parameter designer allows you to configure project-level parameters.
){kind=link}
Figure 4-11 The Add Parameter to Configuration button is found in the parameter designer.
The Manage Parameter Values dialog box will show the list of parameters with values that are currently stored within a Visual Studio configuration, with a column for each configuration in the project. To externalize a parameter, click Add and select the parameter from the list. Figure 4-12 shows a package with a single externalized parameter value (SourceDirectory). The project has two configurations defined: Monthly and Daily. The current configuration (also known as the active configuration) will be indicated in the column header. In this example, the user has set different values for each configuration. The Monthly configuration (which might represent settings to use for a monthly run of the SSIS package) has a value of C:\InputFiles\Monthly. The Daily configuration (which might represent settings to use for daily runs of the package) has a value of C:\InputFiles\Daily. Clicking Sync will copy the value of the active configuration to all other configurations in the project.
)
Figure 4-12 The Manage Parameter Values dialog box.
Project Compilation
When using the Project Deployment Model, building an SSIS project produces a project deployment file (.ispac) that contains the packages, shared connection managers, and parameter information for the project. The default values for parameters within the project are taken from the active Visual Studio configuration.
Deployment Wizard
The Integration Services Deployment Wizard (Figure 4-13) is used to deploy project files (.ispac) to an SSIS Catalog. It can also be used to move projects between different SSIS Catalog instances. The wizard can be launched by double-clicking a project file (.ispac), or by running ISDeploymentWizard.exe from the command line. When using the Project Deployment Model, you can also launch the wizard by right-clicking the project in Solution Explorer and selecting Deploy.
)
Figure 4-13 The Integration Services Deployment Wizard.
Project Conversion Wizard
The Project Conversion Wizard (Figure 4-14) is used to convert SSIS projects in the Package Deployment Model to the Project Deployment Model. One of the main uses of the wizard is to migrate projects created in previous versions of SSIS, so they can take advantage of the new features and functionality in SQL Server 2012.
)
Figure 4-14 The Integration Services Project Conversion Wizard.
Import Project Wizard
The Import Project Wizard allows you to create a new SSIS Visual Studio project (.dtproj) from an existing project deployment file (.ispac), or a project in an SSIS Catalog. The wizard can be launched by selecting Integration Services Import Project Wizard when creating a new project in Visual Studio (Figure 4-15). This process is helpful for editing an existing project if you don’t have the original source files available.
)
Figure 4-15 The Import Project Wizard allows you to create a Visual Studio project from a deployment file.