Step 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 AdventureWorks2008R2 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
If it is not already open, open the Integration Services project that you created in the previous task.
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.
On the Welcome page of the SQL Server Import and Export Wizard, click Next.
On the Choose a Data Source page, do the following steps:
In the Data source list, select Microsoft Excel.
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.
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".
Verify that the Excel version box contains Microsoft Excel 97-2003 and the First Row has column names check box is selected.
On the Choose Destination page, do the following steps:
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>.
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.
In the Database list, select AdventureWorks2008R2.
On the Specify Table Copy or Query page, click Write a query to specify the data to transfer.
On the Provide a Source Query page, in the SQL statement box, type or copy the following SQL statement:
SELECT * FROM [Customers$] WHERE NumberCarsOwned > 0On the Select Source Tables and Views page, do the following steps:
In the Destination list, click [dbo].[Query], and then change the table name, Query, to ProspectiveCustomers.
To edit column metadata and table options, click Edit Mappings.
On the Columns Mappings page, do the following steps:
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
Click OK.
On the Review Data Type Mapping page, accept the default settings and click Next.
On the Complete the Wizard page, review information about the new package and click Finish.
On the Performing Operations page, view the actions that the wizard performs. When finished, the Status column for each action should display Success.
Click Close.
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.
If asked whether to rename the object as well, click Yes.
To set locale sensitive properties of the package
Double click Basic Package Lesson 1.dtsx, click the Control Flow tab, and then click anywhere on the background of the design surface.
On the View menu, click Properties Window.
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
On the Control Flow design surface, click Drop Table(s) SQL Task.
On the View menu, click Properties Window.
In the Properties window, verify that the Codepage property is set to 1252 and the LocaleID property is set to English (United States).
Repeat steps 1-3 for Preparation SQL Task.
To set locale sensitive properties of the Data Flow task
Click Data Flow Task.
On the View menu, click Properties Window.
In the Properties window, verify that the LocaleID property is set to English (United States).
To set locale sensitive properties of data flow components
Double-click Data Flow Task or click the Data Flow tab.
On the Data flow tab, click Source - Query.
On the View menu, click Properties Window.
In the Properties window, verify that the LocaleID property is set to English (United States).
Repeat steps 2-4 for Destination - ProspectiveCustomers.