Help in derived column transformation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Help in derived column transformation

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-11-03 : 15:53:45

Hi All

I m designing SSIS having Data Flow as

Flat File > Derived Column Transformation > Destination Table

but while transfering record from text file to table i need some logic here

my text file is
"ID"|"Name"
1012|"10AA"
1013|"10BB"

logic is 10 should be replace by variable VID = 98

I defined this veriable as int

My ID column in database is int and Name column is varchar(50)

I try here expression like
ID != 98 ? @[User::VID] : ID
it work fine

but for varchar column I am not able to do
I got error when i write expression
SUBSTRING(Name,1,2) != "98" ? [DT_STR, 50, 1252] "(@[User::VID])" + rest of value of column : [Name]

my final output in table should be like
ID..Name
98..98AA
98..98BB

Please help me out

T.I.A

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-11-03 : 16:15:44

HI all

I tried this

SUBSTRING(Name,1,2) != "98" ? (DT_STR,50,1252)(@[User::VID]) + RIGHT(Name,LEN(ChID) - 2) : [Name]

it worked fine :)

Thanks any way

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-11-12 : 03:51:13

Hi,

I need help for importing my text file into DB table

If my text file contain NULL value for numeric data type then I am getting error

my expression for derived column is

ISNULL(ID) ? NULL(DT_Numeric,18,0) : ((SUBSTRING((DT_STR,18,1252)ID,1,3) != "101" && SUBSTRING((DT_STR,18,1252)ID,1,2) != "98") ? (DT_Numeric,18,0)((DT_STR,3,1252)(@[User::tID]) + RIGHT((DT_STR,18,1252)ID,LEN((DT_STR,18,1252)ID) - 3)) : ID)

datatype for ID is numeric

I need to rplace my text "ID" column if there is value other than 101 or 98 (eg. 1012356 replace and get value as @[User::tID] + 2356)

whats wrong with expression syntax

Please help me out

T.I.A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources