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?
> >
> >
>

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?
|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

Import excel file to database

Hai,

I am new to SSIS 2005.

Now i would like to use the foreach loop structure in an SSIS package to
loop through however many Excel files are placed in a directory and
then perform an import operation into a SQL table on each of these
files sequentially.

But i dont know how to get start?

Can anyone guide me on this task?

Thanks.

Step (1) and (2) used from http://rafael-salas.blogspot.com/

If Each Excel File has a Single Work Sheet, The following will be the method:

1) In the foreach loop properties use the following settings

In Collections tab,

Set Enumerator to "Foreach file Enumerator",

Under enumerator Configurations, Specify the Folder and Filter files with *.xls

In Variables Mapping tab,

Under Variable Column Select A Variable Name - User::ExcelFilePath(of String type, ForEachloop Scope) and Index Value 0.

2) Under the Dataflow, Under Source OLEDB Connection manager properties, use the expression builder to assign the Connection String Value to @.[User::ExcelfilePath].

Connect to source to your Destination SQL Server

For the rest of the steps, we pursue from step 11 given by DouglasL'S Answer

Import directory structure to table

I need to be able to import a directory file list into a sql table.
i.e. table contains fields for filename, date etc...
is this possible?
thanks?absolutely. you are going to have to use file system objects with sp_OACreate. A solution was posted here a long time ago but the thread may have been lost when the server crashed.|||absolutely. you are going to have to use file system objects with sp_OACreate. A solution was posted here a long time ago but the thread may have been lost when the server crashed.

Nah...

http://weblogs.sqlteam.com/brettk/archive/2005/06/28/6895.aspx|||That lastone works Brett, but I can't get it to traverse sub-directories

what I am trying to do is index a hard drive of images for a real estate database.|||Drawback of letting SQL Server do this is that the directory must be on de server of SQL has to have access to remote locations.

My first instinct would have been to write a vbs-script (with a filesystemobject and ado connection) that scans the directories and inserts them in the table. This script could be run from any computer that has network access to the SQL Server.|||Lexiflex, the SQL server has access to the iages via a maped drive.

We will use this to link the image to the realtor, the listing and image history. The idea is that we want to be able to take image submissions, identify if there already are images for the property, if not add it to the listing, if there is add a flag to the listing indicating a change etc...

there is another app run from a web page that automatically places the images in the folder, and names, numbers them appropriatly|||So it should be possible to do this in SQL server.

Just curious: How do you identify which newly found image belongs to what property? And do you keep the images on the filesystem or do you import them in the database?|||The images are named with the ListingID#, and are stored in the file system.|||Good luck with this project! It's always fun to build something and see it at work afterwards. :)|||well, just iterate through the Directory table until @.@.ROWCOUNT = 0