Solved

sql server 2005 ssis error when creating a derived column

Posted on 2011-09-30
6
772 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
  • 3
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 334 total points
Comment Utility
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
Comment Utility
Just put

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

no need to set FullName.

0
 

Author Comment

by:blossompark
Comment Utility
Hi Icohan and PatelAlpesh,
thanks for your responses,
     will try your suggestions now and update you later
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:blossompark
Comment Utility
Hi Icohan and PatelAlpesh,
both options produce the same error as i had initially...
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 334 total points
Comment Utility
"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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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 …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now