Hello All, I am importing a flat file into sql server thru ssis 2008. The flat file contains 0x00 and 0x20 values in each of the row. Flat source reads the row upto these values and ignores the values after this. But this should not happen in my case i.e.. it should read the entire row. For flat file source, connection used is flat file connection which reads the file with column delimeter as CR LF and data type as byte stream(DT_BYTES) Please help to get read of this issue. Thanks,harin

I think to resolve you need to: Create a Script Task that will replace the 0x00 (NULL) and 0x20 (SP) chars with say empty space Load your file using the FFS normally (no need for the byte stream My judgment is based on me thinking why would you need those chars anywhere? PS: Instead of the script task you can do something else to strip the non-printing chars out, but I want to confirm this concept with you first.Arthur My Blog

Thanks for your quick reply. Basically i need this chars because after reading the row, have to validate the row length and if it is ok then use conditional split to spilt column level and do some validations at column level and put it in DB. Can you please suggest the other work around your reffering to. Thanks,harin

Then why not to also do the record length validation also in the same script? I am thinking you can use a script transformation set as source. Please read "Parsing Non-Standard Text File Formats with the Script Component" http://msdn.microsoft.com/en-us/library/ms345160.aspx Does it give you an idea or directions to go?Arthur My Blog

The problem i have is while reading the each row from flat file source itself. It is actully truncating the data after 0x00 and 0x20 character. As stated above this should not happen. Instead read entire row till end. Refering to link http://msdn.microsoft.com/en-us/library/ms345160.aspx problem occurs at step 8 Add a Flat File Source to the data flow and configure it to use the RowDelimitedData connection manager. On the Columns page of the Flat File Source Editor, select the single available external column. Here the entire row is not fetched....i.e.. for example suppose if i have row with length 172 chars and values 0x00 and 0x20 occur at 124. Then read upto 124 not the entire 172 chars. Please suggest.Thanks,harin

You need to take in this file as binaryArthur My Blog

If your referring to flat file connection manager(which is provided to flat file source) , i have already provided Data Type as byte stream (DT_BYTES) in Advanced properities. Thanks,harin

No, you will want to read your file as binary using say C# from within a script task similarly to what is posted here: http://www.dotnetperls.com/binaryreaderArthur My Blog

Is there any other alternate without using script task. I mean any options with Flat file souce or flat file connection manager. Where I can provide custom delimeter or some thing similar to this.Thanks,harin

The row delimiter remains CRLF in your case, why would you change it?Arthur My Blog

I wanted to know if there are any options with Flat file souce or flat file connection manager where i can prevent the it from read upto 0x00 and 0x20 instead read it till the end of row.Thanks,harin

Just type in the delimiter you want into the Header row delimiter drop-down box, this simple.Arthur My Blog

Can you please post the script task code to perform this task.Thanks,harin

I have tried reading the file with streamreader, binaryreader but not able to read the file with special character (\0 binary value is 00 and 20). Can you please post the code for script task to read the line including this special character. Thanks,harin