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)
Microsoft SQL Server 2008Windows Server 2003

Avatar of undefined
Last Comment
victordr

8/22/2022 - Mon
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)
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
Jim Horn

>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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
victordr

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

ASKER
Import Error
Jim Horn

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).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
victordr

ASKER
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 Horn

>" 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.
victordr

ASKER
Is there a way to find the relationship inside studio manager?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim Horn

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.
victordr

ASKER
Vendor table
This is what i have.
Jim Horn

Yep.  PK_VENDOR_ID is your primary key on the ID column, and IX_VENDOR_COMPANY is your foreign key to the COMPANY table.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
victordr

ASKER
So i need to insert the data in the IX_VENDOR_COMPANY?
ASKER CERTIFIED SOLUTION
victordr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
victordr

ASKER
How can i search all tables for a certian word?
victordr

ASKER
I resolved it.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23