SolvedPrivate

Using the  Derived Column Transformation editor in an SSIS 2008 Data Flow

Posted on 2014-04-01
5
30 Views
Last Modified: 2016-02-10
Hi,
I am trying to replace  the following  string pattern
0 0 0 0

Open in new window

with NULLs  in an SSIS Data Flow. In a Derived Column Transformation I have entered the following expression
[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

but the pattern is still getting through.
Any help appreciated. Thanks
0
Comment
Question by:blossompark
  • 5
5 Comments
 

Author Comment

by:blossompark
ID: 39971596
below is a grab of the Data VIewer showing the still present values
t
0
 

Author Comment

by:blossompark
ID: 39971639
Here is  the input and output to the transformation task
and the expression in the task used to filter this column
[MSTUB-xInt_Only-Rev-Dt] == "00000000" || [MSTUB-xInt_Only-Rev-Dt] == "0 0 0 0" || [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

ipop
0
 

Author Comment

by:blossompark
ID: 39971664
tried this also, result is the same as above
[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
 

Accepted Solution

by:
blossompark earned 0 total points
ID: 39972416
USing TRIM solves the issue
TRIM([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: 39982471
USing TRIM solves the issue
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

680 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