Flat File Prob in SSIS

I am having a problem importing a flat file in SSIS. The problem is that
there are 3 columns of data but not all columns are filled in. If the last
column is null then an error occurs and it seems like the CrLF does not work
.
The data seems to wrap. Note that the data below shows that there is not a
comma (delimiter) at the end of the line on the Cats line item.
Example data
Item Yr 2005, Yr2004
Dogs, 100 ,125
Cats, 200
Fish, 100 ,50
Does anyone know how to get around a null value at the end of a line when a
comma delimiter is missing?
Thanks in advance
Stewart Rogers
DataSort Software, L.C.The Cats line item should look like: -
Cats, 200,
The missing comma indicates that ther are only 2 columns in the row when all
the rest have 3. The provider of the data should ensure that NULL's are
represented correctly
Paul
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:90E20435-CDC5-4DB2-A4BB-F554CC6041FC@.microsoft.com...
>I am having a problem importing a flat file in SSIS. The problem is that
> there are 3 columns of data but not all columns are filled in. If the
> last
> column is null then an error occurs and it seems like the CrLF does not
> work.
> The data seems to wrap. Note that the data below shows that there is not a
> comma (delimiter) at the end of the line on the Cats line item.
>
> Example data
> Item Yr 2005, Yr2004
> Dogs, 100 ,125
> Cats, 200
> Fish, 100 ,50
>
> Does anyone know how to get around a null value at the end of a line when
> a
> comma delimiter is missing?
> Thanks in advance
> --
> Stewart Rogers
> DataSort Software, L.C.|||Thanks for the reply Paul
Thanks for the reply Paul
The sample data in not the real data. I am recieving a file that has up to
92 fields. It is a multi record type file in which some records are expected
to have 92 fields and other records are expected to have less information.
The record types are identified by a key. For Example
A Records have 92 fields
B records have 12 fields
Thus the B records do not contain 80 trailing commas.
Any thoughts on this
Thanks,
Stewart Rogers
DataSort Software, L.C.
"Paul Smith" wrote:

> The Cats line item should look like: -
> Cats, 200,
> The missing comma indicates that ther are only 2 columns in the row when a
ll
> the rest have 3. The provider of the data should ensure that NULL's are
> represented correctly
> Paul
> "Datasort" <Datasort@.discussions.microsoft.com> wrote in message
> news:90E20435-CDC5-4DB2-A4BB-F554CC6041FC@.microsoft.com...
>
>|||Stewart,
You might give the Jet driver a try. Last I checked, it was
better at importing ragged input. See
http://groups.google.com/groups/search?q=skass+ragged
Steve Kass
Drew University
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:EA9A41D3-6DE5-4D94-94E5-8AA26F00AC3A@.microsoft.com...
> Thanks for the reply Paul
> Thanks for the reply Paul
> The sample data in not the real data. I am recieving a file that has up
> to
> 92 fields. It is a multi record type file in which some records are
> expected
> to have 92 fields and other records are expected to have less
> information.
> The record types are identified by a key. For Example
> A Records have 92 fields
> B records have 12 fields
> Thus the B records do not contain 80 trailing commas.
> Any thoughts on this
> Thanks,
> Stewart Rogers
> DataSort Software, L.C.
>
> "Paul Smith" wrote:
>|||Care to elaborate on the Jet driver solution you're suggesting?
Isn't the Jet driver for Access files?|||Jet is the back end database for MS Access. It is a really great driver in
that it talks to everything ... and I mean everything. It is not as fast as
others but the flexibility is unparallel.
I have MS Access installed on my machine so I am not sure that jet ships
with SQL 2005. I have not been able to connect to the jet driver via SSIS
connection manager and I do not see jet available. I tried ODBC as well as
others and was not able to do a “Jet” connect.
One solution is to create a linked server using the Jet database. See the
following URL to understand how to create a linked server.
http://msdn2.microsoft.com/en-us/library/ms175866.aspx \. Near the bottom i
s
some code to show how to connect a text file. Note that text files have a #
sign to replace the dot in the file name. Example: MyFile.Txt is referenced
as MyFile#Txt. The url also talks about a Schema.ini file but I was able to
read the data without the file. In my case the file names change every day.
So writing a Schema.ini every day was out of the picture for me.
The driver is dynamic in that if a new text file shows up then you will be
able to access the driver.
Another solution to my problem was to create a script task that added commas
to the lines that did not have enough commas. Then I added a flat file
source in the data flow section.
Stewart Rogers
DataSort Software, L.C.
"dianos" wrote:

> Care to elaborate on the Jet driver solution you're suggesting?
> Isn't the Jet driver for Access files?
>|||Thanks Jet won't be an option for me then. And adding commas would be
only a temp solution...
So far in my searching I found this topic on MS forums where MS admits
they've done this on purpose... and that they most likely will have it
working again in the next version.
http://forums.microsoft.com/MSDN/Sh...=67416&SiteID=1
As a temp development solution I have a DTS importing data from comma
delimited files, then a SSIS grabs the data from sql2000 and does it's
stuff...
There is also a free CSV reader in c# here
http://www.codeproject.com/cs/datab...926#xx1410704xx
I haven't found anything SSIS friendly nor easy to use yet. It looks
like I'll have to write it myself :)