Tutorial: Exporting from ODBC to a Flat File Using a Wizard

In Microsoft Integration Services, the SQL Server Import and Export Wizard enables you to quickly create a package that extracts data from a source, and then loads that data into a destination. The data source and destination can be anything from flat files to SQL Server databases to custom sources. This tutorial shows you how to use use the SQL Server Import and Export Wizard to extract data from an ODBC source and load the extracted data into a text file.

What You Will Learn

In this tutorial, you will use the SQL Server Import and Export Wizard to create a package that exports data from an ODBC data source. This ODBC data source is the Sales.SalesTerritory table in the AdventureWorks sample database. You will then have the SQL Server Import and Export Wizard configure the package so that the package writes the data from the ODBC data source to a text file.

After you complete the SQL Server Import and Export Wizard, you will open the resulting package in the SSIS Designer in Business Intelligence Development Studio. (The SSIS Designer is a graphical tool for building complex packages.) After verifying that the package is configured correctly, you will test the package by running the package in Business Intelligence Development Studio.

Requirements

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

To use this tutorial, your system must have the following components installed:

• SQL Server with the AdventureWorks database must be installed on the computer. By default, the sample database is not installed when you install SQL Server and Integration Services. To install this sample database, see Considerations for Installing SQL Server Samples and Sample Databases.

• You must have permission to access the AdventureWorks database.

• A text file must be installed on the computer. The text file is installed on the computer when you install the completed version of the package. If you cannot find the text file, try to install the sample package again. To install the sample package, see Considerations for Installing SQL Server Samples and Sample Databases.

  Note

  When you install the text file that the tutorial uses, you also install the completed version of the package.

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 might be connected automatically when you add that element 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. To configure the auto-connect features, in Business Intelligence Development Studio, on the Tools menu, click Options. Then, in the Options dialog box, in the Business Intelligence Designers section, change the auto-connect settings.

Estimated time to complete this tutorial: 30 minutes

Lessons in This Tutorial

Note

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

• Lesson 1: Creating a SQL Server ODBC Data Source
  In this lesson, you use the SQL Server DSN Configuration Wizard to create the ODBC data source that you use in this tutorial.

• Lesson 2: Creating an Integration Services Project to Export from ODBC to a Flat File
  In this lesson, you create the Integration Services project from which you will run the SQL Server Import and Export Wizard.

• Lesson 3: Running the Wizard to Export from ODBC to a Flat File
  In this lesson, you run the SQL Server Import and Export Wizard to create a package that extracts data from an ODBC source and loads the extracted data into a text file.

• Lesson 4: Testing the Lesson Package
  In this lesson, you run the package in Debug mode to test how the package runs.

Completed Tutorial Package

The completed package for this tutorial is available to be downloaded. You can use this completed package as a reference while you complete the tutorial.

For information about how to download and run the completed package from this tutorial, see the Integration Services samples available on CodePlex.

Stay Up to Date with Integration Services For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet: Visit the Integration Services page on MSDN Visit the Integration Services page on TechNet For automatic notification of these updates, subscribe to the RSS feeds available on the page.