Creating, altering or resaving a table in Execute Sql Task

Question

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

---

Fig000

Answer 1

You can have computed column in your orginal table to do this job.

---

Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

Answer 2

No need of all these.

if you know how it is going to come from the source then you can use a derived column in the DFT itself to get the two columns and load directly in one step.

Regards,Eshwar