Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server 2005 ssis error when creating a derived column

Posted on 2011-09-30
6
Medium Priority
?
884 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 1336 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 664 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 1336 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 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