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):
![enter image description here]()
Now, Go To Edit menu strip >> Character Panel
![enter image description here]()
Now the ASCII character panel is shown, double click on the NULL value in order to add it to the text:
![enter image description here]()
Now the text file will looks like:
![enter image description here]()
You can use the following link to download the file:
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:
![enter image description here]()
All NUL characters are removed:
![enter image description here]()
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:
![enter image description here]()
![enter image description here]()
Also the following screenshot shows how the NUL values are removed from the source file after running the Script Task:
![enter image description here]()
References
