SQL Server 2016 Integration Services Tutorial – Part 2 Package Basics
Although the Import/Export Wizard (covered in Part 1 of this tutorial series) is a quick and easy way to move data between two data stores, the real power of SSIS comes with creating projects and packages in SQL Server Data Tools (SSDT).
In this series of tutorials, you'll see how to build and use SSIS packages. In the first tutorial, we looked at The SQL Server Import and Export Wizard and then briefly looked at adding the package created by the wizard to an SSIS project. In this second tutorial, we will walk through the process of creating a package from scratch. Although this tutorial was designed for SQL Server 2016, the majority of the steps will work with any version 2012 or higher.
In this SSIS Tutorial, you will learn:
Exercise Files: Including the sample database and a completed project
Set up Required:
Starting with SQL Server 2012, Microsoft changed the paradigm surrounding SSIS. In previous versions, although you created a project to store the packages, each individual package was deployed on its own. You could pass values for parameters from one package to another, but you could not share the same parameter across packages.
Additionally, each package maintained its own connection managers. This package centric paradigm caused a lot of duplication of effort and made managing complex projects with many packages much harder to maintain post deployment.
Starting with SSIS 2012, the project deployment mode was introduced. With project level deployment, you have the ability to use a shared parameter across off of the packages in a project. You can also share connection managers across the project. Along with these changes came the requirement to deploy the entire project, rather than single packages.
Starting with SSIS 2016, in addition to deploying an entire project, you can also re-deploy a single package from within the project, or add a stand-alone package to an existing project.
Creating a Package
The Import and Export Wizard is easy to use, but it only taps a small part of the functionality of SSIS. To really appreciate the full power of SSIS, you'll need to use SQL Server Data Tools (SSDT) to build an SSIS package. A package is a collection of SSIS objects including:
You'll see how to build each of these components of a package this series of tutorials, but first, let's launch SSDT and create a new SSIS package.
To create a new SSIS project and package, follow these steps:
Working with Connection Managers
SSIS uses connection managers to integrate different data sources into your packages. SSIS includes a wide variety of different connection manager types that allow you to move data from place to place. The majority of the tasks in SSIS require a connection manager to work.
Table 1 lists some of the connection manager types that ship with the product. Additional connection manager types are available for download through Microsoft and several partners. Additionally, you can develop your own custom connection manager types.
Table 1 : Available Connection Managers
To create a connection manager for a single project, you right-click anywhere in the Connection Managers area at the bottom of the Package Designer and then choose the appropriate connection manager type from the shortcut menu as shown in Figure 2. If the type of connection manager you want to create in not available on this menu, click New Connection, and then proceed as you would with a project level connection manager.
To add some connection managers to your project and package, follow these steps:
Note: If you did not complete the setup at the beginning of the tutorial, you will need to attach the SSISSample database mdf file provided in the files for this tutorial.
Figure 7 shows the SSIS package with the three Connection Managers defined. Notice that SSIS indicates the project connection managers.
In this tutorial you created a new SSIS project and created several connection managers. In the next tutorial, you will learn about control flow tasks and precedence constraints.