Solved

sql server 2005 ssis error when creating a derived column

Posted on 2011-09-30
6
832 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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