SQL Server 2016 Integration Services Tutorial – Part 1
Microsoft says that SQL Server Integration Services (SSIS) "is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing." A simpler way to think of SSIS is that it's the solution for automating data movements. SSIS provides a way to build packages made up of tasks that can move data around from place to place and alter it on the way. The majority of your package design work will be done in SQL Server Data Tools (SSDT). In this series of tutorials, you'll see how to build and use SSIS packages.
In this first tutorial we'll look at a simpler facet of SSIS: The SQL Server Import and Export Wizard. 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:
To accomplish the exercises in this tutorial, you will need the following:
Items created during this tutorial:
The Import and Export Wizard
Though SSIS is almost infinitely customizable, Microsoft has produced a simple wizard to handle some of the most common ETL tasks: importing and exporting data with only minor changes along the way. The Import and Export Wizard protects you from the complexity of SSIS while allowing you to move data between any of these data sources:
Because moving data between Excel and other data stores typically requires data type conversions, many people find the Import and Export Wizard a good place to start, even when needing more complex transformations that can be added later once the wizard creates the base package.
You can launch the Import and Export Wizard from the Tasks entry on the shortcut menu of any database in the Object Explorer window of SQL Server Management Studio, from within SSDT, or as a stand-alone application from Windows.
To import some data using the Import and Export Wizard, follow these steps:
USE master; CREATE DATABASE SSISSample;
Figure 1: Selecting tables to import
Figure 2: Import Wizard results
Adding Packages to a Project
Once you have created a package by using the Import and Export Wizard, you can then import that package into a project to add additional features or simply to maintain the code in some sort of source control such as GitHub or Team Foundation Server (TFS).
Use the following steps to create a new project in SQL Server Data Tools and add the package you created early in this tutorial to the project.
Figure 3: Creating a new project
In the next tutorial, you will build a package from the ground up by using SQL Server Data Tools.