Link to home
Start Free TrialLog in
Avatar of victordr
victordr

asked on

Error importing data into SQL 2008 R2 Express database..

I am trying to import data from a txt file into a SQL 2008 R2 Express databse using the import wizard and i am recieving the following errors.

- Validating (Error)
Messages
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "COMPANY" with a length of 50 to database column "COMPANY" with a length of 30.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "CONTACT" with a length of 50 to database column "CONTACT" with a length of 30.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "PHONE" with a length of 50 to database column "PHONE" with a length of 30.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "FAX" with a length of 50 to database column "FAX" with a length of 30.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Description" with a length of 50 to database column "DESCRIPTION" with a length of 30.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ID".
 (SQL Server Import and Export Wizard)
 
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
 (SQL Server Import and Export Wizard)
 
Error 0xc004706b: Data Flow Task 1: "component "Destination - VENDOR" (42)" failed validation and returned validation status "VS_ISBROKEN".
 (SQL Server Import and Export Wizard)
 
Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
 (SQL Server Import and Export Wizard)
 
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
 (SQL Server Import and Export Wizard)
Avatar of victordr
victordr

ASKER

So i figured out i needed to change the column width and ran the import again and now i only getting these errors.

- Validating (Error)
MessagesError 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ID".
 (SQL Server Import and Export Wizard)
 
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
 (SQL Server Import and Export Wizard)
 
Error 0xc004706b: Data Flow Task 1: "component "Destination - VENDOR" (42)" failed validation and returned validation status "VS_ISBROKEN".
 (SQL Server Import and Export Wizard)
 
Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
 (SQL Server Import and Export Wizard)
 
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
 (SQL Server Import and Export Wizard)
Avatar of Jim Horn
>Data Flow Task 1: Truncation may occur due to inserting data from data flow column "COMPANY" with a length of 50 to database column "COMPANY" with a length of 30.

Check the data times of the source and target column, the source is likely a varchar(50) and the target is likely a varchar(30), so this is the non-fatal 'Hey you might lose data' message.

>Failure inserting into the read-only column "ID".   (SQL Server Import and Export Wizard)
Check your target ID column.  If it's an Identity field, then you can't insert data into it.
I believe an exception is to execute the below line, but you're playing with fire..

SET IDENTITY_INSERT YourSchema.YourTable ON
>Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
 (SQL Server Import and Export Wizard)

You'll probably have to re-map source and target inside your data flow task.  SSIS is famous for forcing developers to remap with any change in either the source or target schema.
How can i skip inserting data into the ID column then? Sorry i am no SQL guru here.
User generated image
In the part of the Wizard that maps source and destination you should see how each column gets mapped between source and target.  Likely the wizard atomatically tried to map a source table, column named ID, to the target table, column named ID.

You'll have to find that mapping and remove it.

This also begs the question, do you actually want that column to be in the target table somewhere?  If so, you'll have to figure out how to handle it, either by creating another column, or forcing the values into the ID, then re-establishing the identity (again, playing with fire here).
okay i removed the mapping and the data is showing up in the application, but when i click on it to edit it it informs me " joe training does not exist in the Vendor lookup table" so there is another table i need to insert this data into?
>" joe training does not exist in the Vendor lookup table
Your target database has a relationship between whatever table you're inserting data into and Vendor, such that if you dump a 'joe training' row into the table that's supposed to map to Vendor, but the Vendor record is missing, then it would be a foreign key violation.

Again, you'll have to figure out how you want to handle it.  Guessing you'd have to do a pump before this one, where you add any necessary Vendor rows, then you pump data into the intended source table.

For example, you have a new source of data that has Orders, but these orders are for products that you don't have in your Products table.  Add those new Products first, then add the orders.
Is there a way to find the relationship inside studio manager?
In SSMS, if that's what you're asking, you can use the left-side treeview to navigate to any table, and eyeball the subfolders for Keys and constraints.
User generated image
This is what i have.
Yep.  PK_VENDOR_ID is your primary key on the ID column, and IX_VENDOR_COMPANY is your foreign key to the COMPANY table.
So i need to insert the data in the IX_VENDOR_COMPANY?
ASKER CERTIFIED SOLUTION
Avatar of victordr
victordr

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How can i search all tables for a certian word?
I resolved it.