SSIS: Flat File Row Delimiter Not Recognized

[JJ of Eugene OR]

SSIS Community:

I have some .txt files I need to work with in SSIS. When I create a Flat
File connection, the connection manager does not seem to recognize the row
delimiters. For example, in a file that contains two rows, all I see in the
preview pane is one long row, as if the connection manager doesn't see the
carriage return/line feed at the end. Instead, the preview pane (and columns
pane) shows two black boxes in place of each of the row delimiters.

Some things to note:
1) I can look at the exact same files in DTS, and DTS reads the file
perfectly. In other words, my DTS package sees the row delimiters just fine.
2) I have created other flat file connections in other SSIS packages
without problem. One difference between flat files which have worked and
flat files which have not is that the problem files were generated by a
mainframe. The files that have worked OK were generated from an MS
Access/Jet file. (I don't have control over this. I'm just hoping that
knowing that the files were generated by a mainframe might trigger an idea
for someone.)

Why would DTS get it right and not SSIS? What can I do to get SSIS to work
right?

All help much appreciated!
- JJ

[Wei Lu]

Hi JJ,

Thank you for using MSDN Managed Newsgroup Support.

Please try to use other Row Delimiters to see whether this issue appear.

Another workaround is create a SQL 2000 DTS package and using the Execute
DTS 2000 Package task in SSIS package.

Execute DTS 2000 Package Task
http://msdn2.microsoft.com/en-us/library/ms137907(d=ide).aspx

Please let me know the result. I look forward your reply.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[JJ of Eugene OR]

Wei:

I should have mentioned in my original posting I did already try different
row delimiters. The only box where I could see an option for specifying a
delimiter was a box labeled 'header row delimiter'. This doesn't sound like
a true 'row delimiter' (my file doesn't even have a *header* row), but it is
the only input box that talks about delimiters.

The default header row delimiter is {CR}{LF}. I also tried the one that
says {CR} only, the one that says {LF} only. and the one that says 'tab' (out
of desperation since there is no tab at the end of each row). The other
options (comma, colon, etc) definitely do not apply since I can see/know that
those physcially visible characters do not exist at the end of each row.

FYI: I also tried different code pages in the hopes that might solve the
problem. I didn't try all of the different options, but I tried several
different ones.

I can't create the step as a DTS package. I do not have control over how
the server is set up. My co-worker who does have the control refuses to
install the back-ward compatible DTS feature on our SQL Server 2005 machine.
We had this conversation a while ago. While I do not agree with my
co-workers reasons for this decision, there is one way in which I am happy
about it. I don't want to start creating DTS pieces as part of a long-term
solution anyway. DTS is going away. I have been working so hard to learn
SSIS and re-create from scratch a bunch of work that I had already done in
the past just to upgrade to SQL Server 2005. I don't want to go through all
that and then have to go and create something (a DTS task) that I know is
already obsolete. The Point: Even if I had the DTS option available to me
(which I don't), I would strongly prefer another alternative.

I couldn't find reference to this flat file problem on the web. Do you know
if it is a known problem with SSIS? Or do you think I am doing something
wrong? Do you know why my SQL Server 2000 DTS package can read the same file
correctly that my SQL Server 2005 SSIS package can't read correctly? Can you
think of something else I can try?

Thanks,
- JJ

[SP]

Hi JJ,

I am little relaxed that i am not the only one who suffered thet same way as
you are, scartching your head all the time. beleive me i have spend 3 weeks
to find a way to load a simple flat file to a sql table and couldn't suceed.
SSIS is so poor in handling a flat file load, and doesn't do any data
masaaging like DTS. i got exactly the same problem like yours and also many
more like length mismatch, unicode/nonunicode etc. My company gave up to
migrate to SSIS until we feel comfortable. I think the only way to get around
with your problem at this moment is what Lu suggested using SQL DTS 2000
task. If your co-worker don't agree, tell them to figure it out a work around
with SSIS task.

Post by JJ of Eugene OR
I should have mentioned in my original posting I did already try different
row delimiters. The only box where I could see an option for specifying a
delimiter was a box labeled 'header row delimiter'. This doesn't sound like
a true 'row delimiter' (my file doesn't even have a *header* row), but it is
the only input box that talks about delimiters.
The default header row delimiter is {CR}{LF}. I also tried the one that
says {CR} only, the one that says {LF} only. and the one that says 'tab' (out
of desperation since there is no tab at the end of each row). The other
options (comma, colon, etc) definitely do not apply since I can see/know that
those physcially visible characters do not exist at the end of each row.
FYI: I also tried different code pages in the hopes that might solve the
problem. I didn't try all of the different options, but I tried several
different ones.
I can't create the step as a DTS package. I do not have control over how
the server is set up. My co-worker who does have the control refuses to
install the back-ward compatible DTS feature on our SQL Server 2005 machine.
We had this conversation a while ago. While I do not agree with my
co-workers reasons for this decision, there is one way in which I am happy
about it. I don't want to start creating DTS pieces as part of a long-term
solution anyway. DTS is going away. I have been working so hard to learn
SSIS and re-create from scratch a bunch of work that I had already done in
the past just to upgrade to SQL Server 2005. I don't want to go through all
that and then have to go and create something (a DTS task) that I know is
already obsolete. The Point: Even if I had the DTS option available to me
(which I don't), I would strongly prefer another alternative.
I couldn't find reference to this flat file problem on the web. Do you know
if it is a known problem with SSIS? Or do you think I am doing something
wrong? Do you know why my SQL Server 2000 DTS package can read the same file
correctly that my SQL Server 2005 SSIS package can't read correctly? Can you
think of something else I can try?
Thanks,
- JJ

[JJ of Eugene OR]

SP:

I appreciate your response. It is nice to know I am not alone in my
struggles. It sounds like you had even more problems with SSIS than I've
been having, and I've been sweating on almost every tiny little thing that I
need to do--even after spending 2 weeks doing nothing but trying to get over
that learning curve. I went through tutorials, articles, and the wrox book.
I'm only posting a small fraction of the problems that have occupied hours
and hours of my time as I try to re-create my DTS packages in SSIS. You
might agree with me when I say that I don't think the product was ready for
prime time. I've found it to be buggy, feature-lacking in some serious ways,
and in too many cases the very opposite of user-friendliness (user-hostile?
user-punnishing? user-neglectful?).

I thought it only fair though to share that I have a different take on the
situation than you do. Even with all that I said above, I think SSIS is
going to be way better than DTS. The very structure with the control-flow
separated out from the data flow just makes so much more sense! It's great
having the connections set up all in one place (so that they can all be
changed in one place). The way we can use expressions for the task
properties is wonderful. Using expressions to decide which task to run next
is also so very cool. There some important cool features in SISS that have
allowed me to (relatively easiliy) add some new error-checking features in my
packages that I didn't have before but always wanted to do. And there's a
lot more that is great about SSIS. I think that SSIS is very well
thought-out in some respects. Assuming Microsoft continues to work on it, is
going to be a great product sometime in the future.

I can't say for anyone else whether it is worth it or not to struggle with
SSIS as it stands now. I can understand why your company gave. Even with
all my struggles and pressures on the job to move on to my next project (they
don't understand what I am doing since I'm not giving them any new
functionality), I'm still personally glad that I'm getting to upgrade to SQL
Server 2005 with SSIS. I just think there is something basically right about
SSIS. (At least until I hit the next SISS problem. <grin>)

- JJ

[Wei Lu]

Hi JJ,

Thank you for the update.

Firstly, to specify the Row Delimiter, please click the Columns in the left
panel in the Flat File Connection Manager Editor. You will see the row
delimiter. Please try to use other delimiter to see does this issue occur.

Secondly, would you please send the text file which has problem to me for
troubleshooting? I understand the information may be sensitive to you, my
direct email address is ***@ONLINE.microsoft.com (Please remove the
ONLINE when you send email), you may send the file to me directly and I
will keep secure.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[JJ of Eugene OR]

The group might want to know that I just sent Wei Lu the file he asked for.

Of more interest: I also sent Wei a picture of the Flat File Connection
Manager screen when 'columns' is selected in the left pane. I do not see a
box that says 'row delimiter'. Why would I not see the box? Maybe I have a
bad or old copy of the program? (I didn't install it.)

- JJ

[JJ of Eugene OR]

Wei:

New info! I played around some more with the 'Flat File Connection Manager'.
If I go to the 'general' tab and change the 'format' from 'fixed width' to
'delimited', then I can see a 'row delimiter' option on the 'columns' page.
Maybe that is my problem? It's not behavior that I would expect, but maybe
SSIS doesn't recognize rows when it is fixed width? Maybe you have to
specify the row width manually first before setting individual column widths?
It may not be ideal, but it is a do-able work-around. Did I hit apon a
'this behavior is by design' thing or is there still something else that
could be done to make SSIS recognize the end of the row?

As for my problem file, if I do choose 'delimited', it does recognize the
end of the row. So, as I'm 'talking' to myself here, I'm thinking that maybe
I just didn't understand the SSIS thinking for fixed width formats. Sound
about right?

Thanks,
- JJ

[Wei Lu]

Hi JJ,

Thank you for the update. I got your email and since you have got progress
I will reply here.

According to the Books online, the flat file can be in delimited, fixed
width, or mixed format.

1. Delimited format uses column and row delimiters to define columns and
rows.

2. Fixed width format uses width to define columns and rows. This format
also includes a character for padding fields to their maximum width.

3. Ragged right format uses width to define all columns, except for the
last column, which is delimited by the row delimiter.

Since you want to use the delimiter to specify the row, I suggest you use
the Ragged right Format.

I tested your file on my side. If I use Ragged right Format, your file will
be 2 rows and I canuse width to define columns.

Please try to test on your side and if you have any further questions,
please feel free to let me know.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[JJ of Eugene OR]

Wei:

You are right. If I use 'ragged right', each data row is displayed as it's
own row on the screen.

Question:
What does it mean when you say the fixed width format, 'also includes a
character for padding fields to their maximum width.' What character? Is it
something I need to define somewhere? Or are you just talking about SSIS
inserting spaces in the last column if the row comes up short? I didn't see
a reference to this padding character in the Books On Line, so I'm not sure
what you are getting at. I'm trying to decide which format I should choose
since I could technically get either 'fixed width' or 'ragged right' to work
for my data. Maybe understanding the 'padding character' thing will help me
decide which way to go.

A thought to float in the wilds: (not aimed specifically to Wei)
The choices for 'format' is confusing terminology. I have seen the term
'fixed width' in many other applications, including DTS, Access, Excel, etc.
The term always seemed to mean data having columns of the same width. I've
never had trouble with any of those other applications recognizing the row
delimiter when I worked with importing a 'fixed width' file.
In fact, when I had once seen similar import problems to the one I was
having here in SSIS, it meant that something was wrong with the data (as
opposed to something wrong in the choice I picked from a drop down). My
point is that what is normal behavior for 'fixed width' in SSIS was mimicking
error behavior I have seen elsewhere, specifically DTS. That makes things
confusing.
Along the same lines, since my data has the same width for all of the
columns/rows, it would not have occurred to me to pick a choice labeled
'ragged right'.
I certainly 'get it' now. I appreciate having the concept behind
'ragged right' available. I can even see why (if there weren't an outside
world of applications like DTS, Access, Excel, etc.) it might seem to make
sense to choose the terms 'fixed width' and 'ragged right' as the drop down
choices. However, there is still nothing in the Books On Line that lead me
to choose 'ragged right'. The BOL descripton for fixed width simply says,
'Columns have a fixed width.'
That describes my data perfectly. The BOL description for 'ragged right' is:
'Ragged right files are those in which every column
has a fixed width, except for the last column. It is delimited
by the row delimiter.'
Notice the 'except for last column' part. Even my last column has a fixed
width. 'Ragged rigth' just didn't seem to apply. The point is: After
reading BOL, I still picked 'fixed width' as the appropriate choice and
wondered why it 'wasn't working.' Maybe BOL could be clarified? Even
better, could some helpful text be added to the dialog box in addition to a
nice BOL discussion of what the terms really mean in terms of practical use
with real data? (Or maybe I'm just too dense, and it all makes perfect sense
to everyone else.)
I'm kicking myself now for not trying ragged right earlier. But given
the bigger picture, I also think it was an understandable mistake.
= Just some thoughts aching to be expressed.

Thanks again for your help.

- JJ

[Wei Lu]

Hi JJ,

Thanks for the update.

As for the padding character, based on my scope, it is the SSIS inserting
spaces in the last column if the row comes up short.

The fixed width in SSIS is different with other like DTS or Excel. In DTS,
it will use the row delimiter to seperate the row. But in SSIS, it does not.

I suggest you use the ragged right since it is more like the 'fixed width'
behavior in Excel and DTS which you are familiar with.

To provide your feedback directly to the product groups:
http://lab.msdn.microsoft.com/productfeedback/default.aspx

Thank you!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[sqllion]

To get the workaround and Step by Step description for developing SSIS package in order to overcome the issue with SSIS while importing text files with Flat File Connection Manager and Flat File Source where the "Row Delimiter" property does not work properly for rows having NULL or empty values, follow the below link:
http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/
Thanks,
SQL Lion


From http://www.developmentnow.com/g/103_2006_5_0_0_755788/SSIS-Flat-File-Row-Delimiter-Not-Recognized.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

[SQL Lion]

To get the workaround and Step by Step description for developing SSIS
package in order to overcome the issue with SSIS while importing text files
with Flat File Connection Manager and Flat File Source where the "Row
Delimiter" property does not work properly for rows having NULL or empty
values, follow the below link:
<a href=”http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/” >
http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/ </a>
Thanks,
SQL Lion


"unknown" wrote: