Post reply

Adding table columns and updating them in Execute Sql Task

  • November 18, 2012 at 11:11 am

    #265991

    Hi,

    Relatively new to SSIS. I have a table that I've created in a data flow. Back out in the control flow, after the table has been created, I am trying to add two fields to this table which will hold the first and last name for a record since the name in this table has one field with both first and last names; I need to split them. All of the attempts below were done in an Execute Sql task.

    First I tried to alter the table using an alter table command in the Execute Sql Task. I used this to add the two fields. Then I did an update statement with the split of the single name field into the new fields. I got an "SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size" error.

    I then tried to save the table to a temp table (one pound sign) and then alter the temp table to have the two new fields. Then I tried to update the fields. I got the same "SqlCommand.Prepare" error as above.

    Then I tried to create a view which selected all the fields from my table and, in the select statement for the view, I add two substring commands (as fields in the select) to split the single name, first extracting the first name and then extracting the second name. When I tried to create the view I was told that I had a syntax error near the word "view". I moved the create command to SSMS and it ran fine:

    create view dbo.MyView

    as

    select *, SUBSTRING(EmployeeName, 1, CHARINDEX(' ', EmployeeName) - 1) as FirstName,

    SUBSTRING(EmployeeName, CHARINDEX(' ', EmployeeName) + 1, LEN(EmployeeName)) as Lastname

    FROM dbo.MyTable

    ;

    GO

    Any Advice would be appreciated.

    Thanks,

    Fig000

  • November 18, 2012 at 1:10 pm

    #1560121

    It would be useful background info to have some idea of what your source and destination are - is this a flat file to SQL Server import, or something different?

    I would probably be looking into using derived column transformations within your data flow to break up the name column.

    SSIS is highly dependent on design-time meta data, so doing things like adding run-time columns will often be unworkable.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply