SSIS Derived Column Expression to skip null/blank value and take the next digits of string value

Question

I am very new to SSIS and I am performing a task where I have to take the flat-file text document and then derive the columns with specific digit lengths. For eg, lines in the text document are:

101001A00000000000000309493020111139                                     112
101001A00000000000000309493020111139112

I am writing derived columns such that certain digit lengths become my columns

Expression	Derived Column Name
substring([column 0],1,3)	record1
substring([column 0],4,6)	record1

I want to skip the null values here in order to take the value 112 how do write an expression for this? For a non-null scenario, I can proceed with the same substring function but I want to handle these blank spaces or null values too.

Can someone please help?

Answer 1

You should use two derived column transformations in that scenario:

1. The first is to remove extra blank, you can use the following expression:

REPLACE([column 0]," ","")

If the word NULL is written as a text, you can use the following expression:

REPLACE(REPLACE([column 0]," ",""),"null","")

2. The second derived column transformation takes that output and split the lines into separate columns.