The error message is pretty clear - you have a portion of your test file being put into a fields being called "su_supplier_code" and the data you are importing from...

XACT_ABORT ON specifies that if an error is encountered any open transaction will be rolled back.

You still need to have an open transaction.

Depending on connection settings you may have a...

There is an article right in the daily newsletter for today that is close to what you are referring to:

http://www.sommarskog.se/share_data.html#XML

This outlines some techniques for transferring table data between stored procedures.

If you need the list of VendorItems being inserted, you could use an OUTPUT clause on the insert statement, but I don't think you need to get that complex.

You can...

Avoid the dynamic SQL and use an appropriate parsing function if you have to do this.

A better approach is not to pass delimited lists around. If you are designing...

Yes

BEGIN TRAN

BEGIN TRY

---- SQL Statements ---

COMMIT TRAN

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

BEGIN

...

I assume you used an OLEDB Destination for the insert. You could use an OLEDB Command for the update.

1 million rows for this type of operation may be a...

Assuming you have built a database schema with referential integrity, you would have to populate dimension records before fact records or you would violate foreign key constraints.

In most data warehouse...

Yup - more confused.

The point of a lookup is to get a field from another data source by a matching field, so I do not understand what you are saying.

Also,...

don't believe everything you read.

Remove the @ symbol

State In('CA', 'UT', 'AZ', 'WY')

"State" is referring to your field name.

Either I am not following, or there are two really obvious answers.

The first is to include the invoice number in your data set for the good and bad records and...

You could use a UDF.

Then you can modify the function if you need to make a change.

By the looks of the application and user name from sp_who2, you are the problem.

You have management studio open with an open transaction locking the table you are trying to...

I would suspect a blocking process.

Try "select * from obs_result (NOLOCK)"

If this works, you have another process with a lock on at least one row in the table. You...