• Article

Tutorial: Creating a Basic Package Using a Wizard

Microsoft Integration Services provides the SQL Server Import and Export Wizard for building packages that perform data transfers. These packages can extract data from a source and load it into a destination, but the package can perform only minimal data transformation in the transfer process. In addition, the wizard is a quick way to create basic packages that can then be enhanced in SSIS Designer.

In this tutorial, you will learn how to use the SQL Server Import and Export Wizard to create a basic package. The package that you create extracts data from an Excel workbook and loads it into a table in the AdventureWorks2008R2 database. The table is defined as one of the steps in the wizard and then created dynamically when you run the package.

In subsequent lessons, the package will be expanded to include a data flow that sorts the data, creates a new column, and populates the column with values. To generate the new values, you will learn how to use the new Integration Services expression language together with the graphical expression builder to write an expression that creates new values based on existing data columns.

When you install the sample data that the tutorial uses, you also install the completed versions of the packages for each lesson of the tutorial. By using the completed lesson 1 package, you can skip ahead and begin the tutorial with lesson 2 if you like. If this is your first time working with packages, the SQL Server Import and Export Wizard, or the new development environment, we recommend that you begin with lesson 1.

What You Will Learn

The best way to become acquainted with the new tools, controls, and features available in Microsoft SQL Server Integration Services is to use them. This tutorial first walks you through the SQL Server Import and Export Wizard to create a basic data-transfer package, and then shows you how to enhance the data transformation capabilities of the package by using SSIS Designer.

Requirements

This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in SQL Server Integration Services.

To use this tutorial, your system must meet the following requirements:

  • You must run the package that this tutorial creates in 32-bit mode. This sample uses the Microsoft Jet 4.0 OLE DB provider, for which there is no 64-bit version. The package fails if you run it in 64-bit mode. For more information about running packages in 32-bit mode on a 64-bit computer, see 64 bit Considerations for Integration Services.

  • SQL Server with the AdventureWorks2008R2 database must be installed on the computer. To enhance security, the sample databases are not installed by default. To install the sample databases, see Considerations for Installing SQL Server Samples and Sample Databases.

  • You must have permission to create and drop tables in AdventureWorks2008R2.

  • The sample data must be installed on the computer. The sample data is installed together with the samples. If you cannot find the sample data, return to the procedure above and complete installation as described.

Note

When reviewing tutorials it is recommended you add Next and Previous buttons to the document viewer toolbar. For more information, see Adding Next and Previous Buttons to Help.

This tutorial assumes that you have not reconfigured SSIS Designer to use auto-connect features between control flow elements or between data flow elements. If SSIS Designer uses auto-connect, an element may be connected automatically when added to the design surface. Also, the auto-connect feature for control flow supports the use of Failure and Completion as the default constraint, instead of Success. If SSIS Designer is not using Success as its default constraint, you should reset this configuration while doing the tutorial. You configure the auto-connect features in the Business Intelligence Designers section in the Options dialog box that is available from Options on the Tool menu.

Estimated time to complete this tutorial: 1 hour

Lessons in This Tutorial