Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?Do you have an extra line in your text file?
>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> >--Original Message--
> >I am trying to import data into a table in my database
> using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_80] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very
> last line in the TAB
> >file, giving an error that it cannot insert a NULL value
> into [SIC_ID] for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why
> is it trying to
> >insert a NULL value when none exist in the TAB file to
> insert?
> >
> >
> >.
> >|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
> >I am trying to import data into a table in my database using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very last line in the
TAB
> >file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why is it trying to
> >insert a NULL value when none exist in the TAB file to insert?
> >
>|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> > I am trying to import data into a table in my database using DTS.
> >
> > The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> > The import procedure works, but then fails at the very last line in the
> TAB
> > file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> > the last line in the TAB file.
> >
> > This is correct as the fields are all NOT NULL, but why is it trying to
> > insert a NULL value when none exist in the TAB file to insert?
> >
> >
>