February 14, 2006 at 4:45 pm
I have a fixed-length flat file that contains about 30 columns. I have got it pretty well figured out using the flat file connection tool, but I am having trouble with the end of the line.
I know when I look at the file it is a CrLf that separates the rows, and SSIS only seems to understand this to a certain extent. It knows to go to the next line, but it also adds two rectangles to the lines, like this:
Col1 Col2 Col3 Col4 Col5
aaaaaa bbbbb ccccc ddddd eeeee
??aaaaa bbbbb ccccc ddddd eee
ee??aaaaa bbbbb ccccc ddddd e
eeee??aaaaa bbbbb ccccc ddddd
While this does create a cool pattern, it is a pain in the butt. The only solution I have found is adding two more spaces to the last column in the table, but the ?s just get appended there.
If anybody has any clue how to get rid of them, that would be great.
Thanks in advance
February 15, 2006 at 10:22 am
If anybody can actually figure this one out, great.
But for now I've just used a SUBSTR function in a Derived Column task to get around this, so it's not an urgent matter.
February 15, 2006 at 10:39 am
I had the same issue. I posted the error in the forums and everyone told me to import to Access. If it works there then export it again and try it in SQL.
The problem with that approach was I could never figure out the issue. Every month I need to import the data into Access and export it. Then it works. It must have to do with the system exporting the data.
February 15, 2006 at 3:04 pm
I do have it working without the derived column, now.
If you just edit the Flat File Connection and add another column that is two characters wide, you can just choose not to include this column when you set up the Flat File Source Task.
Just name it something (like EOLChars), set both the output and input length to 2, and make sure it is a String type. Then, under the columns tab of the Flat File Source, make sure you uncheck the box for this column.
I hope this helps JKSQL, but if you have any more questions feel free to email me.
February 15, 2006 at 3:38 pm
I will look into that. not sure if it will work on this case. But maybe.
February 16, 2006 at 1:43 pm
I also received a suggestion to use the "Ragged Right" file type instead of fixed-length.
I haven't tried it yet, but you may want to.
February 17, 2006 at 6:29 am
Ditto to the Ragged Right. That was the only solution that worked for me. I also ended up having to define the end of record marker as a seperate column and just excluding it (unchecking the box) from the import to the destination. For some reason it would pull into the last column and screw up the data transfer. Of course I think I had this issue with the old DTS. My text files are coming from a Z/OS mainframe.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply