Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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 @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1386094
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse