Solved

sql server 2005 ssis error when creating a derived column

Posted on 2011-09-30
6
822 Views
Last Modified: 2012-05-12
Hi,
    I am creating a package in SSIS that imports data into a sql server 2005 database table from a csv file.
The csv file has columns, first name, last name and I want to create a derived column  called Full_ name.
ssisThe expression I am using is SET Full_Name = first name +' ' + last name
which works fine in t-sql  but I am getting the following error in ssis..

Any help appreciated. Thanks

TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [Derived Column [115]]: Attempt to parse the expression "SET Full_Name= first name+' ' +last name" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Error at Data Flow Task [Derived Column [115]]: Cannot parse the expression "SET Full_Name= first name+' ' +last name". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [115]]: The expression "SET Full_Name= first name+' ' +last name" on "output column "Full_Name" (263)" is not valid.

Error at Data Flow Task [Derived Column [115]]: Failed to set property "Expression" on "output column "Full_Name" (263)".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
 
0
Comment
Question by:blossompark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 334 total points
ID: 36893463
Try something like this:

SET Full_Name= [first name]+' ' +[last name]

if SQL objects have space or other special chars in their name they must be enclosed in sqare brakets.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 166 total points
ID: 36895484
Just put

[first name]+' ' +[last name]

no need to set FullName.

0
 

Author Comment

by:blossompark
ID: 36902459
Hi Icohan and PatelAlpesh,
thanks for your responses,
     will try your suggestions now and update you later
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:blossompark
ID: 36902478
Hi Icohan and PatelAlpesh,
both options produce the same error as i had initially...
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 334 total points
ID: 36904906
"The expression I am using is SET Full_Name = first name +' ' + last name
which works fine in t-sql  but I am getting the following error in ssis.."


Sorry to say but this is not possible in SQL query. The expresion above should be something like for T-sql to work:

UPDATE TABLE table_name SET Full_Name = [first name] +' '+ [last name]

or if you are using a variable should be something like:

SET @Full_Name = (SELECT [first name] +' '+ [last name] )


And BTW your full_name is defined as varchar (50) as pe above schreenshot so if first name +' ' + last name is longer than that you know what hapens.

good luck!
0
 

Author Closing Comment

by:blossompark
ID: 36947185
Hi Icohan and PatelAlpesh,, sorry for my slowness in addressing this  as of late...I have not returned to the issue yet but will use your comments when i do so...thanks again
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question