How to Write NULL character (Hex 00) in File using SSIS
I was working on project where we were receiving some Hex Data in character form from MainFrame System and our goal was to transfer data into SQL Server and then write back into flat file in same format after massaging some data. Our main difficulty was to write NULL data in flat file.
For example: One Column that had some value with NULL data coming as in flat file.
If you take this data to NotePad then you will not see above shown character it will show up as no value but when you open same file into Hex Editor or other tool like TextPad then you can see actual character representation of NULL character where you will find Hex Value for this character is 00
As everybody does, I too did try to search on google for solution but there were no solution that could help me to get resolution. There were many posts with same problem but did not find any resolution. Finally, I started using C# script in SSIS and got to solution that produced exactly same character what MainFrame system was writing into file. Here is step by step by what I did for resolution:
In below guideline, I am covering part when data messaging is done in database and now it is time to write data onto file from database.
I transformed 1 column value into 4 columns and transferred to database that ensured that if I have different character in that column then also I am transferring data correctly. Because when you transfer data for column that has character hex value for NULL then complete value becomes NULL and you will not have correct data into your database.
Create Database connection (1), Drag and Drop OLE DB Source (2) and Script Component (3). Script component can be either Destination or Transformation. Since, this article focused on handling NULL hex character so I am skipping Connection and OLE DB Source and jumping directly to Script Component that helped me to write NULL character in file. So lets move to Step 2.
Double click on Script Component and select C# as shown below:
Now Click on Edit Script in above screen and write following script inside ProcessInputRow function as shown below:
public override void Input0_ProcessInputRow(Input0Buffer Row)
Add your code here
FileStream fs = new FileStream(@"C:\Mohan\Result.txt", FileMode.Append, FileAccess.Write);
I am sure you will have data in other columns as well that you would like to write along with these value. If it is then you can do so by using script below inside same function used above.
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
bytes = encoding.GetBytes(Row.OtherColumn1);
fs.Write(bytes, 0, bytes.Length);
In order to use scripts used in solution you need to use to following namespace in top:
Overall Script should be as shown below:
Hex 00 that is NULL character can be written in Flat file in same way as MainFrame system writes, using Script Component in SSIS package