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:
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
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.
|Note: The images in this tutorial were captured with SSDT for VS 2015 and the project defined with the SQL Server 2016 compatibility level.|
Figure 3: Creating a new project
Figure 4: Load an existing package
|If you try and run this package, it will fail. The first step creates tables that now exist in your SSISSamples database. You can right-click the Preparation SQL Task 1 task and click Disable. The package will now run successfully, but you will have twice as many rows in each table.|
In the next tutorial, you will build a package from the ground up by using SQL Server Data Tools.
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