April 1, 2008 at 6:32 am
Hi,
I have a "Data Flow" task which contains two steps, "OLE DB Source" then a transformation into a "Flat File Destination"
The "Data Flow" task extracts data from a table and outputs the data into a comma delimited flat file. Problem is that some of the columns contain NULLs, and the "OLE DB Source" or the transformation to the "Flat File Destination" is removing the NULLs, something like ISNULL(AccountDescription, '').
Does anyone how to enable the outputting of NULLs when exporting data to a flat file?
Is there some kind of setting, of property that I have overlooked in the package or the above mentioned tasks?
Many Thanks
April 1, 2008 at 7:16 am
Had a chat to our resident dba's, they came up with a solution.
In the code that extracts the data, you just need to replace the NULL's with a NULL, see code below.
Old code which did not output the nulls
SELECT AccountNumber,
AccountStatus,
AccountOpenDate
FROM ExtractData;
--------------------------
OUTPUT
AccountNumber,AccountStatus,AccountOpenDate
1234567,15,20080101 09:59:59 AM
1234533,,20080103 12:23:59 PM
1234567,16,
New extract code, which outputs the NULLS
SELECT AccountNumber,
'AccountStatus' = ISNULL(AccountStatus, 'NULL'),
'AccountOpenDate' = ISNULL(AccountOpenDate, 'NULL')
FROM ExtractData;
---------------------------
OUTPUT
AccountNumber,AccountStatus,AccountOpenDate
1234567,15,20080101 09:59:59 AM
1234533,NULL,20080103 12:23:59 PM
1234567,16,NULL
April 1, 2008 at 7:32 am
hey sarg3 i think it can be easily done by turning on the flag in flat file source editor where u haqve an option return nulls if you check that option it gives u null when there is no value ....u need not go for queries ...k if u dnt find let me know i shall send u a screenshot
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply