I have a flat file that has fields delimited by tilde {~} and rows delimited by {CR}{LF}. The problem is, my source is spitting out the {NULL} (&H00) at the end of the row.
The data displays correctly in the pre-viewer (with a non-print symbol at the end of the last field), but gives an error when I try to run the package.
Example:
“data”~”data”~”data”{NULL}{CR}{LF}
I can process the row in c# using:
“\0\r\n”
as the row delimiter. What is the equivalent notation for SSIS
Thanks,
How do you specify special characters as flat file delimiters?
Rafael Costa
The weird thing is that ascii 0 does not exist. I cannot create a test file.
Can you send me a test file with a dozen rows so I can give it a try thanks.
Philippe
p.cand at onsemi.com
T2k
Hi,
So you found how to type ~ in the column delimiter.
Now you have to add a derived column transform that replaces the last column with this expression.
SUBSTRING([Column 2],1,LEN([Column 2]) - 1)
By the way, I build a test file with an ascii 7 at the end of the last column ([Column 2]) but never got an error... What ascii number is at the end of your rows
Philippe
renaud.besnard
It is ascii 0 (HEX 00), which doesn't really exist as a character. This is causing the row to error out in the data source. So, I can't do a SubString to trim it off.
The weird thing is that it is able to read the row while I am in the designer. Then when I execute the package, every row errors out as having the last field too long.
It there a way to specify non-printable delimiters other than those given Can I specify an ascii, hex, or escape character some how
Thanks for helping with this!