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.
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:
Figure 1 : Empty SSIS package
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
Connecting to ADO objects such as a Recordset.
Connecting to data sources through an ADO.NET provider.
Connecting to a cache either in memory or in a file
Connecting to Data Quality Services servers and databases.
Connecting to an Analysis Services database or cube.
Connecting to an Excel worksheet.
Connecting to a file or folder.
Connecting to delimited or fixed width flat files.
Connecting to an FTP data source.
Connecting to an HTTP data source.
Connecting to a Microsoft Message Queue.
Connecting to a set of files, such as all text files on a particular hard drive.
Connecting to a set of flat files.
Connecting to an ODBC data source.
Connecting to an OLE DB data source.
Connecting to a server via SMO (Server Management Objects.)
Connecting to a Simple Mail Transfer Protocol server.
Connecting to a SQL Server Mobile database.
Connecting to Windows Management Instrumentation data.
Starting with SQL 2012, you can define both project level and package specific connection managers. If more than one package in a project will utilize a particular connection manager, it is beneficial to create it as a project level connection manager to be shared among the different projects. To create a project level connection manager, right-click the Connection Managers folder in Solution Explorer, select the appropriate connection manager type, and then provide the required information. Each Connection Manager has its own custom configuration dialog box with specific options that you need to configure.
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.
Figure 2 : Connection Manager Shortcut Menu
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 3 : Connection Manager
Figure 4: Defining a Flat File Connection Manager
Figure 5: Flat File Connection Manager
Figure 6: Properties
Figure 7 shows the SSIS package with the three Connection Managers defined. Notice that SSIS indicates the project connection managers.
Figure 7: An SSIS package with two 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.
In-Depth SSIS Training
For in-depth SSIS training, click here to view all of Accelebrate's SSIS training courses for you and your staff.
Accelebrate’s training classes are available for private groups of 3 or more people at your site or online anywhere worldwide.
Don't settle for a "one size fits all" public class! Have Accelebrate deliver exactly the training you want, privately at your site or online, for less than the cost of a public class.
For pricing and to learn more, please contact us.Contact Us Train For Us
New York City
Salt Lake City