Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Adding table columns and updating them in Execute Sql Task Expand / Collapse
Posted Sunday, November 18, 2012 11:11 AM


Group: General Forum Members
Last Login: Wednesday, October 16, 2013 8:49 AM
Points: 19, Visits: 118

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

select *, SUBSTRING(EmployeeName, 1, CHARINDEX(' ', EmployeeName) - 1) as FirstName,
SUBSTRING(EmployeeName, CHARINDEX(' ', EmployeeName) + 1, LEN(EmployeeName)) as Lastname
FROM dbo.MyTable


Any Advice would be appreciated.


Post #1386073
Posted Sunday, November 18, 2012 1:10 PM



Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 7,981, Visits: 19,117
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.

Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

If your posting includes some T-SQL code, please surround the code with IFCode formatting tags. It helps readability a lot.
Post #1386094
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse