Reputation: 338

SSIS Flat file could not deal with NUL (\x00) value?

I am trying to load data from text files to database. My source files contain null character NUL somehow (Picture1).

I just make all the fields as one column (delimited with {CR}{LF}). Then I　do the preview of the data.

The data is just what we need. But then when I run the package, the data changed, not like what I see in data preview. I added a data viewer to see the data.

The number 1 disappear in the first row (see the red). It seems that flat file reading ends at NUL character. But my Row delimiter is {CR}{LF}, it doesn't make sense the number 1 in the end disappear. Can anyone tell me why is that?

Upvotes: 6

Answers (2)

Hadi

Reputation: 37313

Reproducing the error

First of all, I would like to show the steps to reproduce this error using Notepad++ editor.

I created a text file called TestNUL that contains data similar to the screenshot posted in the question (commas are placed where NUL objects should be):

Now, Go To Edit menu strip >> Character Panel

Now the ASCII character panel is shown, double click on the NULL value in order to add it to the text:

Now the text file will looks like:

You can use the following link to download the file:

TestNUL.txt

Removing NUL character using Notepad++

To remove this character you can simply open Notepad++, Click Ctrl + H to open the Find and Replace dialog. Then select to use Regular Expressions and replace \x00 with an empty string:

All NUL characters are removed:

Find and replace in multiple file

If you are looking to find and replace this character in multiple files, then you can use notepad++ to do this using Find in Files feature:

Automating the process Within SSIS

Since the issue occurs at run-time not while previewing data, you can simply add a Script Task before the data flow task to replace all \x00 values with an empty string. You can read the text file path from the flat file connection manager or you can store it in a variable. You can use a similar C# code:


public void Main()
{
    string FilePath = Dts.Connections["SourceConnection"].ConnectionString;

    string text = System.IO.File.ReadAllText(FilePath);
    text = text.Replace(Convert.ToChar(0x0).ToString(), "");
    System.IO.File.WriteAllText(FilePath, text);

    Dts.TaskResult = (int)ScriptResults.Success;
}

If you are working with large text files then you can use System.IO.StreamReader and System.IO.StreamWriter classes to read the file line by line using ReadLine() function.

Experiments

I created a package and added two flat file connection manager, the source reads from TestNUL.txt file and the destination create a new TestNUL_edited.txt file with the same structure. I added a Script Task with the code above and added a data viewer in the Data Flow Task, the following screenshot shows how the rows are not corrupted: