2005 import wizard from flat file - problems with mapping to existing table

Question

Post reply

2005 import wizard from flat file - problems with mapping to existing table

petescutt

Valued Member

Points: 66
More actions
December 5, 2007 at 5:05 am

#119854

We have an existing manual process to load data from a flat file onto existing tables of a sql 2000 database using the Import/export wizard.
I cannot do this on sql 2005.
If I run it on my pc I get the error "The product level is insufficient for component ". Running it directly on the server overcomes this problem.
I used the Import/Export wizard on the server and it fails due to a mismatch between the column formats it has assumed for the flat file and the actual column formats of the table being loaded on to. This happens whether or not I use the "Suggest Types" button to get it to suggest data types.
Is it possible to make it assume the data types of the target table as it seems to do under sql2000 DTS? Or do you have to manually define the columns within the wizard?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Powichrowski Marek Hall of Fame Points: 3423 More actions · Answer 1

Please explain "step by step" what you want to this SSIS package do.

petescutt Valued Member Points: 66 More actions · Answer 2

There is no package involved. I am simply using the wizard to run the import and run it "immediately", I am not saving it.

The input file has the first line as column headings. The column delimiter is ";". There are about 60 tables to load. There are existing tables on the database which are appended to. It worked ok on 2000 database, but after upgrade it doesn't.

Under 2000 it is a simple process of stepping through the screens via the wizard:

Data Source - text file

select file format

specify column delimiter

Choose a destination

Select source tables and views

Then choose - run immediately

It works

I do the corresponding ones for 2005

Data source - flat file

Under advanced I choose "suggest types" otherwise sql chooses string width 50

Choose a destination - the database name

Select source tables and views - I choose "Edit mappings" otherwise SQL wants to create a table (and fails because there is one there)

Execute immediately

It fails with errors on truncating fields

Hope this is enough info

Powichrowski Marek Hall of Fame Points: 3423 More actions · Answer 3

Try to add derived column component between source and destination and explicitly define data types (and length for string) for each column on the destination side.

petescutt Valued Member Points: 66 More actions · Answer 4

That is what I don't want to do as it would take so long. Under sql2000 it picked up that information from the table being appended to. Under sql2005 it is aware of the table (you can choose it from the drop down list) but it doesn't use it.