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
Solved

sql server 2005 ssis error when creating a derived column

Posted on 2011-09-30
6
794 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
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:blossompark
ID: 36902478
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
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change variables in SQL table 6 93
Set the max value for a column 7 39
Addition to SQL for dynamic fields 6 47
Email Notifications for SQL 2005 9 35
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 …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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