Replacing NULL characters from within a flat file

Here's one for the group:

I have a fixed-width flat file. Within some of the rows, I have embedded NULL characters. The inherit problem is that NULL characters are string terminators, so using a flat file source doesn't allow the capturing of these NULL characters or any characters after the first NULL character -- only the string up to the NULL character.

So, within SSIS, what would be the best way to replace NULL characters with a SPACE character? My file is fixed-width, and replacing with a space will allow me to keep the length the same. I am not opposed to running a script task against the file first (before using my flat file source), but would need some guidance as I'm not a .Net guru, by any means.

Unfortunately, going to the bank to have them correct this file has proved fruitless. We're going to have to deal with these characters on our side.

Thanks,
Phil

Just curious, what does a null character look like in a flat file? I've encountered parsing problems in some basic BAI bank files that were comma delimited. Basically, I couldn't bulk insert or bcp the data in through SQL scripting, but had to use ActiveX to import the data or the SQL 2000 (probably 2005 also) DTS Text Connection Object (because somehow DTS ignored the problem characters and found the end-of-line return like it is supposed to). The basic run-down on using ADO/ActiveX can be found here: http://msdn2.microsoft.com/en-us/library/ms974559.aspx.

Anyway, if you come across another solution please let me know.

Good luck!

|||A NULL character is a hex 00.|||Bump|||

Hi Phil,

I would write a tiny C program to do the conversion and then call it from the SSIS package.

BTW, what is the semantic of these embedded nulls in string fields, are they some kind of dividers?

Thanks,

Bob

|||Have also encountered this problem and wondered if anyone has written any code to change the ASCII NULL character to a SPACE or another harmless printable character ?