INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SSIS Derived columns

SSIS Derived columns

(OP)
I need help getting the following case statement into the derived column format in SSIS


CASE
WHEN SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(Data AS VARCHAR(9)), 4, 6) IS NULL
THEN 0
ELSE RTRIM(SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(Data AS VARCHAR(9)), 4, 6))
END

Here are examples for the data in Data
row 1 - 1201303
row 2 - 123456789
row 3 - null
row 4 - 12345678
the first row result would be 201303
row 2 result would be 456789
row 3 result would be 0
row 4 result would be 345678

RE: SSIS Derived columns

Why you need derived column? Why not just include that case in your select or update statement and run on database site

RE: SSIS Derived columns

(OP)
Thanks gk53, so you would say a select into another table with the case statements would be faster than using a Data flow task with derived columns, what I am looking for is increased performance. I am not sure in derived columns will be a heavier workload than a straight select into. Note we are taking about 700,000,000 records in some cases

RE: SSIS Derived columns

CODE

(DT_STR,9,1252)(TRIM(data) == "" || UPPER(TRIM(data)) == "NULL" ? (DT_STR,9,1252)"0" : (DT_STR,9,1252)(SUBSTRING(REPLICATE("0",9 - LEN(data)) + data,4,6))) 

assumptions are
the input is a file, not a sql server table - if it is a sql server then the convert is better done on the server side unless that server is under heavy CPU pressure - in this case SSIS should not run on that server anyway.
that the "null" value you mentioned on your example is either a empty string or word "null"
if it is a file contents of data do not contain leading zeros - if it does part in red needs to be changed

Note that with volumes you are talking about you should consider the following

1 - split flow into parallel loads into final table (which should be partitioned) - at least 5 can run at the same time, more if done properlly.
2 - if input is a file see if extract can be split into several files instead of a single one. if a single one use a conditional split to split input into its parallel logical files
3 - derived columns transformation - set as many as required so no more than 10 fields are being transformed in each one - e.g. transform1 - 10 fields, transform2 - 10 fields. flow becomes datasource->transform1->transform2->destination

4 - fine tune buffers/rows per buffer
5 - fine tune data type validation if you are certain input data is always valid

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close