SolvedPrivate

Editing a Derived Column Transformation Expressions in SSIS  2008 r2 SSIS package

Posted on 2014-03-31
2
43 Views
Last Modified: 2016-02-10
Hi,
I have the following Derived Column Expression in a  Data Flow Transformation in an SSIS package
[MSTUB-xInt_Only-Rev-Dt] == "00000000" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)

Open in new window

This (I THINK)  replaces  values == "00000000" with NULLs
I now want to filter out “0 0 0 0” values also.
How do I amend the expression to accommodate this? Is it possible to add some kind of “OR”  to it?
Any guidance appreciated, thanks
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
2 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39968545
Is it possible to add some kind of “OR”  to it?

That's indeed possible.  The OR in SSIS expressions is a double pipe: ||

So your expression would be something like this:

[MSTUB-xInt_Only-Rev-Dt] == "00000000" || [MSTUB-xInt_Only-Rev-Dt] == "0 0 0 0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)

Open in new window

0
 

Author Closing Comment

by:blossompark
ID: 39968733
Great thanks ...Works 100%!!
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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