Export (0) Print
Expand All

Running the Wizard to Create the Basic Package

New: 14 April 2006

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.

  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, in the Data source list, select Microsoft Excel.

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

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

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

  8. Click Next.

  9. On the Choose Destination page, in the Destination list, Select SQL 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 Microsoft SQL Server 2005 on the local computer. To use a remote default instance or a named instance of SQL Server 2005, 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>.

  10. 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.

  11. In the Database list, select AdventureWorks.

  12. Click Next.

  13. On the Specify Table Copy or Query page, click Write a query to specify the data to transfer and click Next.

  14. 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
    
  15. Click Next.

  16. On the Select Source Tables and Views page, in the Destination list, click [AdventureWorks].[dbo].[Query], and then change the table name, Query, to ProspectiveCustomers.

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

  18. On the Columns Mappings page, 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

    nvarchar

    N/A

    10

    Phone

    nvarchar

    No change

    255

    50

  19. Click OK.

  20. Click Next.

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

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

  23. Click Close.

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

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

  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).

  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.

  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).

  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.

Release History

17 November 2008

Changed content:
  • In the first procedure, added a step to select [AdventureWorks].[dbo].[Query] on the the Select Source Tables and Views page and to change the table name.

Community Additions

ADD
Show:
© 2015 Microsoft