Export (0) Print
Expand All

Task 2: Running the Wizard to Create the Basic Package

In this task, you will run the SQL Server Import and Export Wizard to create the basic data transfer package.

  • Specify the data source. You will use the Excel workbook, Customers.xls, as the data source. On a wizard page you will provide a query that selects the customers who own at least one car from the Customers spreadsheet in the workbook.

  • Specify the destination. You will write the data to a table in the AdventureWorks database. The table, ProspectiveCustomers, will be created dynamically. You will define the metadata of the destination columns by using the wizard.

After the wizard completes, you will rename the package and verify the values of certain locale-sensitive properties of the package, tasks, source, and destination. This step is important because the Excel workbook includes locale-sensitive date data in the BirthDate column. If the regional settings on your computer do not specify English (United States), you must update the values of the specified properties or the package cannot be run successfully.

To run the SQL Server Import and Export Wizard

  1. If it is not already open, open the Integration Services project that you created in the previous task.

  2. In Solution Explorer, right-click the SSIS Packages folder and click SSIS Import and Export Wizard. If the Solution Explorer window is not open, click Solution Explorer on the View menu.

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

  4. On the Choose a Data Source page, do the following steps:

    1. In the Data source list, select Microsoft Excel.

    2. Click Browse, navigate to C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Sample Data, click the Excel workbook file, Customers.xls, and then click Open.

    3. Verify that the Excel file path box contains "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Sample Data\Customers.xls".

    4. Verify that the Excel version box contains Microsoft Excel 97-2003 and the First Row has column names check box is selected.

  5. On the Choose Destination page, do the following steps:

    1. In the Destination list, Select SQL Server Native Client, and in the Server name box, type localhost.

      When you specify localhost as the server name, the connection manager connects to the default instance of SQL Server on the local computer. To use a remote default instance or a named instance of SQL Server, replace localhost with the name of the server or server and named instance to which you want to connect. To connect to a named instance use the format <server name>\<instance name>. 

    2. If the instance of the Database Engine that you specified supports Windows Authentication, use the default Windows Authentication mode; otherwise, click Use SQL Server Authentication and type a user name in the User name box and a password in the Password box.

    3. In the Database list, select AdventureWorks.

  6. On the Specify Table Copy or Query page, click 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 * FROM [Customers$] WHERE NumberCarsOwned > 0
    
  8. On the Select Source Tables and Views page, do the following steps:

    1. In the Destination list, click [dbo].[Query], and then change the table name, Query, to ProspectiveCustomers.

    2. To edit column metadata and table options, click Edit Mappings.

  9. On the Columns Mappings page, do the following steps:

    1. Verify that the Create Destination table option is selected, select the Drop and re-create destination table check box, and modify the metadata of the destination columns.

      The following table lists the columns and the metadata changes that you need to make:

      Column name

      Default type

      Updated type

      Default size

      Updated size

      FirstName

      nvarchar

      No change

      255

      50

      MiddleIntial

      nvarchar

      nchar

      255

      1

      LastName

      nvarchar

      No change

      255

      50

      BirthDate

      datetime

      No change

      N/A

      N/A

      MaritalStatus

      nvarchar

      nchar

      255

      1

      Gender

      nvarchar

      nchar

      255

      1

      EmailAddress

      nvarchar

      No change

      255

      50

      YearlyIncome

      float

      money

      N/A

      N/A

      TotalChildren

      float

      tinyint

      N/A

      N/A

      NumberChildrenAtHome

      float

      tinyint

      N/A

      N/A

      Education

      nvarchar

      No change

      255

      50

      Occupation

      nvarchar

      No change

      255

      50

      HouseOwnerFlag

      float

      bit

      N/A

      N/A

      NumberCarsOwned

      float

      tinyint

      N/A

      N/A

      AddressLine1

      nvarchar

      No change

      255

      60

      AddressLine2

      nvarchar

      No change

      255

      60

      City

      nvarchar

      No change

      255

      30

      State

      nvarchar

      No change

      255

      3

      ZIP

      float

      No change

      N/A

      N/A

      Phone

      nvarchar

      No change

      255

      50

    2. Click OK.

  10. On the Review Data Type Mapping page, accept the default settings and click Next.

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

  12. On the Performing Operations page, view the actions that the wizard performs. When finished, the Status column for each action should display Success.

  13. Click Close.

  14. In Solution Explorer, right-click the new package in the SSIS Packages folder, click Rename, and type Basic PackageLesson 1. Make sure that the name includes the .dtsx extension.

  15. If asked whether to rename the object as well, click Yes.

To set locale sensitive properties of the package

  1. Double click Basic Package Lesson 1.dtsx, click the Control Flow tab, and then click anywhere on the background of the design surface.

  2. On the View menu, click Properties Window.

  3. In the Properties window, verify that the LocaleID property is set to English (United States).

To set locale sensitive properties of the Execute SQL tasks

  1. On the Control Flow design surface, click Drop Table(s) SQL Task.

  2. On the View menu, click Properties Window.

  3. In the Properties window, verify that the Codepage property is set to 1252 and the LocaleID property is set to English (United States).

  4. Repeat steps 1-3 for Preparation SQL Task.

To set locale sensitive properties of the Data Flow task

  1. Click Data Flow Task.

  2. On the View menu, click Properties Window.

  3. In the Properties window, verify that the LocaleID property is set to English (United States).

To set locale sensitive properties of data flow components

  1. Double-click Data Flow Task or click the Data Flow tab.

  2. On the Data flow tab, click Source - Query.

  3. On the View menu, click Properties Window.

  4. In the Properties window, verify that the LocaleID property is set to English (United States).

  5. Repeat steps 2-4 for Destination - ProspectiveCustomers.

Task 3: Testing the Lesson 1 Basic Package

Integration Services icon (small) 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:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Community Additions

ADD
Show:
© 2014 Microsoft