June 3, 2008 at 2:21 pm
In SSIS 2005, in a data flow I have an OLE DB source getting data from Oracle,
a Derived Column Task, and a Flat File Destination Task.
In the derived column task, there is a column that could be null.
When the column is not null, I want to write out some data, but if it is
null, I don't want to write out anything for that column. The data is written
to a flat file where the columns are delimited by a colon.
Here is the expression I am working with:
!ISNULL(BROKER_EXEC) ? "88:" + BROKER_EXEC : NULL(DT_WSTR,9)
When broker_exec is Not null in Oracle, I get the string "88:XYZ" for that column in the flat file,
where broker_exec = XYZ.
But when broker_exec is null in Oracle, and the result of !ISNULL(BROKER_EXEC) is False,
and the NULL(DT_WSTR,9) is used for the column in the flat file, yes I get null but the
Flat File Connection Manager inserts a colon, the resulting file for some sample columns
would look something like this: 87:1::761:47008609 as opposed to what I want
which is this: 87:1:761:47008609 What I need is to not get the double colon effect.
The question then is, how do you conditionally skip printing a column even if
the flat file conn manager delimits all columns by a colon, even though a column
is nulled out?
June 3, 2008 at 2:45 pm
...
June 4, 2008 at 2:40 pm
I think what I am asking to do cannot be done the way I want to do it.
My solution now is to remove the column that could be null in the flat file conn mgr
and in the derived column task, and use the ? operator to add in that column to the previous column
in the derived column task in the case where broker_exec is not null.
It just writes out the previous column if broker exec is null.
However if all the columns could be null, this solution would get ugly fast.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply