Task 2: Updating the Execute SQL Task

Task 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 AdventureWorks 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

  1. Click the Control Flow tab.

  2. Double-click Preparation SQL Task.

  3. On the General page, click the SQLStatement property, and then click the browse button (…).

  4. 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 [AdventureWorks].[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)



  5. Click OK.

  6. Click Parse Query. The SQL statement should parse successfully.

  7. Click OK.

Task 3: Adding and Configuring the Sort Transformation

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

© 2016 Microsoft