Creating a Basic Package Using a Wizard
New: 14 April 2006
Microsoft SQL Server 2005 Integration Services (SSIS) 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 AdventureWorks 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 2005 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.
| Lessons | Description |
|---|---|
In this lesson, you will use the SQL Server Import and Export Wizard to create a data-transfer package. |
|
In this lesson, you will enhance the basic package to include a sort operation, and to add a new column and column values to the existing dataset. |
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 2005 Integration Services.
To use this tutorial, your system must have the following components installed:
- SQL Server 2005 with the AdventureWorks database. To enhance security, the sample databases are not installed by default. To install the sample databases, see Running Setup to Install AdventureWorks Sample Databases and Samples.
- You must have permission to create and drop tables in AdventureWorks.
- This tutorial also requires sample data. 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