• Article

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 the basic data transfer package. When you run the wizard, you make the following selections:

  • Specify the data source. You will use a SQL Server ODBC data source as the source and select rows in the table, Sales.SalesTerritory, in the AdventureWorks2008R2 database.

  • Specify the destination. You will write the data to a text file. You will configure the text file by using the wizard.

After you run the wizard, you then rename the package. You rename packages to help make the packages self-documenting.

To run the SQL Server Import and Export Wizard

  1. If it is not already open, in Business Intelligence Development Studio, open the Integration Services project that you created in the previous lesson.

  2. In Solution Explorer, right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.

    Note

    If the Solution Explorer window is not open, on the View menu, click Solution Explorer.

  3. On the Welcome page of the SQL Server Import and Export Wizard, click Next.

  4. On the Choose a Data Source page, make the following selections:

    1. For Data source, select .Net Framework Data Provider for Odbc.

    2. For ConnectionString, type the following text:

      Dsn=name of your SQL Server ODBC data source

  5. On the Choose Destination page of the SQL Server Import and Export Wizard, make the following selections:

    1. For Destination, select Flat File Destination.

    2. Click Browse, navigate to C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Exporting ODBC to Flat File\Sample Data, click the text file, FlatFile.txt, and then click Open.

    3. For File name, make sure that this option is set to "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Exporting ODBC to Flat File\Sample Data\FlatFile.txt".

    4. For Locale, make sure that this option is set to English (United States).

    5. For Code page, make sure that this option is set to 1252 (ANSI - Latin I).

    6. For Format, make sure that this option is to Delimited.

  6. On the Specify Table Copy or Query page of the SQL Server Import and Export Wizard, select Write a query to specify the data to transfer.

  7. On the Provide a Source Query page, in the SQL statement box, type or copy the following SQL statement:

    Select Sales.SalesTerritory.Name, Sales.SalesTerritory.CountryRegionCode, Sales.SalesTerritory.SalesYTD, Sales.SalesTerritory.SalesLastYear, Sales.SalesTerritory.CostYTD, Sales.SalesTerritory.CostLastYear
FROM Sales.SalesTerritory

  8. On the Configure Flat File Destination page, make the following selections:

    1. For Source query, make sure that this option is set to "Query".

    2. For Column delimiter, select Tab {t}.

  9. On the Complete the Wizard page, review information about the new package, and then click Finish.

  10. On the Performing Operations page, view the actions that the wizard performs, and then click Close.

    When finished, the Status column for each action should display Success.

To rename the package

  1. In Solution Explorer, in the SSIS Packages folder, right-click the new package, click Rename, and then type Export ODBC Lesson.

    Make sure that the name includes the .dtsx extension.

  2. If asked whether to rename the object, click Yes.