November 18, 2012 at 11:11 am
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
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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy