CSV file has the NUL character (Ascii 0) in a field. When the data is transformed to SQL Server table, value followed the NUL character is getting truncated. But the source system has such characters and it can not be manipulated in the file. But Oracle accept such characters when loading through Oracle SQL Loader, but failed in loading with SSIS. But the destination system is SQL Server only. So please help.

Could you please provide us your sample input data and desired output? Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

What exactly do you mean with "value is getting truncated"?

Thanks for your response. I have a field value like Break A Pot But there is a hidden character NUL (^@ = 0 = 0x0) after the "A". After analysing the text, I found this is the character with Ascii value 0. Field value is transfered to SQL Server using SSIS. But the SQL Server column only has the value "Break A" and remaining portion after this ascii 0 is truncated.

Hello, Thanks for your response. Find some pipe delimited sample values. Here the 3rd column contains the Ascii 0 (Nul value). When it is loaded to SQL Server using SSIS, it truncates the remaining characters followed by the character with Ascii value 0. I'm not able to attach the file here. so find the text values here. 1000874076|100418708|Break A pot|Break à Pot 1000874077|100418709|Break A pot|Brel à Pot In the actual text file there is a hidden character NUL (^@ = 0 = 0x0) after the "A" in the 3rd field. After analysing the text, I found this is the character with Ascii value 0. Field value is transfered to SQL Server using SSIS. But the SQL Server column only has the value "Break A" and remaining portion after this ascii 0 is truncated. I have tested by inserting the values directly in SQL Server field. Still I get the truncated output. Insert into TestTable (col1) values ('Break A '+Char(0)+' Pot'); Select col1 from TestTable; Break A --Note here the "Pot" is truncated Strange, Len(col1) returns 13. But when loading the data through SSIS, it only inserts the first part 'Break A' only and shows the exact length 8. "Pot" is truncated while loading. Hope the input is clear.

Hi Bilaal, thanks for the clear explanation of the input. Do you know when the truncation occurs? Directly when you extract the data, or when you insert it in SQL Server? If it is directly truncated: use a script task to remove any unwanted characters from the file. (not so efficient, as you will read the whole file twice) If it is truncated in SQL Server, you can clean up in the data flow by using a script component. For every column that can have this problem, you'll need an expression in the following form (VB.NET): sString = sString.Replace(Chr(0), ""c)

Hello Koen Verbeeck, Thanks for your imm response. I just tried with direct insert in SQL server table. But the extract that I have received from the different system has such Ascii 0 value in between the field. I dont want to manipulate the extract received with Replace because I should process the data as it is received. Because the same text is stored in the source system which is running in Oracle. Is there any way to transform such data to SQL Server through SSIS without truncation. Is it a known issue for SQL Server that will behave when chr(0) presence in between values?

If the truncation doesn't occur in SSIS (you can check with a data viewer), then you can use the script component solution that I proposed.

Thanks!!

Hi I am also facing same problem. I have seen in the data viewer the value are getting trucated before inserting into the SQL Table. But if I see preview in the Flat file source connection it seems to be showing correctly without trucating data. Still I do not have solution for it. If anybody solve this issue Please share It will be grate help !! Thanks Manoj

Hi, I am not sure if this is still a problem, but it seems that the characters after the NUL do exist in the DB but are not showing when selected. As mentioned before the length of the data is correct, in fact if you SELECT SUBSTRING('Break A Pot',10,3) it retruns Pot. Also SELECT REPLACE('Break A Pot',char(0), '$') returns 'Break A$ Pot'