Error importing data into SQL 2008 R2 Express database..

victordr
victordr used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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)
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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 to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
How can i skip inserting data into the ID column then? Sorry i am no SQL guru here.

Author

Commented:
Import Error
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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).

Author

Commented:
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?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>" 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.

Author

Commented:
Is there a way to find the relationship inside studio manager?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

Commented:
Vendor table
This is what i have.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Yep.  PK_VENDOR_ID is your primary key on the ID column, and IX_VENDOR_COMPANY is your foreign key to the COMPANY table.

Author

Commented:
So i need to insert the data in the IX_VENDOR_COMPANY?
Commented:
So i restored a snapshot of the VM and importing the file into a fresh DB and it worked just fine. I can edit it in the application also.

Author

Commented:
How can i search all tables for a certian word?

Author

Commented:
I resolved it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial