December 5, 2007 at 5:05 am
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?
December 5, 2007 at 9:51 am
Please explain "step by step" what you want to this SSIS package do.
December 5, 2007 at 11:01 am
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
December 5, 2007 at 1:43 pm
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.
December 11, 2007 at 10:51 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply