Step 2: Updating the Execute SQL Task
In this task, you will update the SQL statement in the Execute SQL task named Preparation SQL Task. The existing SQL statement was automatically generated from the options you specified when you stepped through the SQL Server Import and Export Wizard pages to create the lesson 1 package. This SQL statement creates the Query table in the AdventureWorks2008R2 database when the package is run.
Later in this lesson, you will generate an additional column to the data that is extracted from the Excel spreadsheet, and you need to include a definition of that column in the SQL statement.
To modify the SQL statement
Click the Control Flow tab.
Double-click Preparation SQL Task.
On the General page, click the SQLStatement property, and then click the browse button (…).
In the Enter SQL Query dialog box, add a comma at the end of the line, [Phone] nvarchar (50), press Enter, and on the new line, type [FullName] nvarchar (103).
The completed SQL statement should look like this:
CREATE TABLE [AdventureWorks2008R2].[dbo].[Query] (
[FirstName] nvarchar(50),
[MiddleInitial] nchar(1),
[LastName] nvarchar(50),
[BirthDate] datetime,
[MaritalStatus] nchar(1),
[Gender] nchar(1) NOT NULL,
[EmailAddress] nvarchar(50),
[YearlyIncome] money,
[TotalChildren] tinyint,
[NumberChildrenAtHome] tinyint,
[Education] nvarchar(50),
[Occupation] nvarchar(50),
[HouseOwnerFlag] bit,
[NumberCarsOwned] tinyint,
[AddressLine1] nvarchar(60),
[AddressLine2] nvarchar(60),
[City] nvarchar(30),
[State] nchar(3),
[ZIP] float,
[Phone] nvarchar(50),
[FullName] nvarchar (103)
)
GO
Click OK.
Click Parse Query. The SQL statement should parse successfully.
Click OK.