Hi all, I'm working with a Flat File Source but my file has null (x00) characters in it. I tried passing the data through a Script Component to replace nulls with spaces but by the time the file is read, my data is already chopped off past the null characters. Any ideas on what I can do?

Hello Ashish, I would make such a flat file connection so it sees your input file as a ONE COLUMN record (CRLF is the record separator). Then you may simply use a derived column transformation with SSIS Expression employing the REPLACE function to eliminate the nulls (replace the x00 with an empty string). From there you can output this record to another flat file and from it make yet another FFC that "sees" it as a multi-column file, and finally load it.Arthur My Blog

Arthur's suggestion will replace all nulls with empty strings in one operation. You can also use a derived column transformation just after the flat file source and write an expression for each column to replace nulls with empty strings, something like this: ISNULL([inputColumn])? "" : [inputColumn] you should put your input column name instead of [inputcolumn] in above expression.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Hello Ashish, I would make such a flat file connection so it sees your input file as a ONE COLUMN record (CRLF is the record separator). Then you may simply use a derived column transformation with SSIS Expression employing the REPLACE function to eliminate the nulls (replace the x00 with an empty string). From there you can output this record to another flat file and from it make yet another FFC that "sees" it as a multi-column file, and finally load it. Arthur My Blog I'm using SSIS 9.0 and it doesn't allow the literal "\x0000", it says it's not valid hex. Every other hex works but not null. http://msdn.microsoft.com/en-us/library/ms141001.aspx also says that x0000 is not valid because it's the string terminator. Reza, your solution did not work either, due to difference between SQL null and null character. If I just bulk insert my file into SQL, the column won't be null, it will have non-printable "null" character in it. I think I'm going to just have to clean the input file before any data flow tasks?

I so it is the x0000, not x00 as you told us. Big difference indeed. So yes, you can cleanse the input file as the first step.Arthur My Blog

I so it is the x0000, not x00 as you told us. Big difference indeed. So yes, you can cleanse the input file as the first step. Arthur My Blog What is the difference? I tried "\x00" but it says I need to enter hex in the form of "\xhhhh". If I open my file in hex edit it shows 3 consecutive "00" characters. Also when I preview the file in Flat File Connection Manager preview it shows 3 unprintable characters.

x00 is not the true NULL and it did not ring the bell in my head Ashish. Not sure why you needed to try with \x00, the input file either has it or not, and the Hex editors may show things differently. Not to talk about any other text editors. The main point is the input file is not in operable condition, often, I do not trust such sources, and expect such packages to fail from time to time. I suggest you implement some through cleansing of the file prior to loading it (because such weird chars can even cause headaches down the road when they are in the database). If you want you can even resort to some data cleansing commercial components.Arthur My Blog