ETL and Data Integration tools - Modern ETL software for everyone

Hi,
I am trying to import data from a flat file to a SQL database.
Some records are new and some are updates.
If I use a SQL Server connection type, I can write NEW records fine. But I cannot use SQL because most record are updates so I must use ODBC. When I try to run the import using ODBC it gives me an error: Cannot update identity column MRN. SQL Statements could not be prepared.

I've tried everything I can think of to fix this. How can I get this import to run correctly?

Hello.

Thank you for letting us know about this problem
We will check it and get back to you
I assume that you are talking about Visual Importer

Peter

You are not supposed to update the identity field
This is how SQL server works

The only solution is to drop the IDENTITY property using SQL before, do your UPDATE stuff and recreate the IDENTITY property using SQL after.

I am using Advanced ETL Processor Pro by the way.

I do not want to update that field at all. It automatically increments if you leave it alone.

The problem is, when I use ETL to do an update without setting a value for it, ETL gives the error \"Mandatory field MRN missing\", but if I try to assign a value to it, ETL gives the error \"Cannot update identity column \"MRN\".

Either way I lose!

How can I make ETL just leave the field alone and not give an error?

I see your point now

This issue has to be investigated.

It will take 2-3 days.

We will let you know when it is ready

John

Using version 2.3.0.6 standard cannot reproduce the problem

Loading data from a text file via odbc into sql server

table creation script

CREATE TABLE [dbo].[U1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Desc1] [varchar](50) NULL,
[Item] [varchar](50) NULL,
[f1] [numeric](3, 1) NULL,
[D] [datetime] NULL
) ON [PRIMARY]


using desc1 one as update key

nothing is mapped to [ID] field in transformer

Here is the log

Information 18/02/2009 22:04:42 Starting Transformation...
Information 18/02/2009 22:04:42 Preparing Writers...
Information 18/02/2009 22:04:42 Writer: {}, [Table=DEMO.dbo.U1] Writer { } Is Ready
Information 18/02/2009 22:04:42 All Writers are Ready
Information 18/02/2009 22:04:42 Processing Data...
Information 18/02/2009 22:04:42 Reader:{} Found: 1 File(s) to read
Information 18/02/2009 22:04:42 Reader:{} Path: C:\
Information 18/02/2009 22:04:42 Reader:{} Mask: test_data_comma_delimited_small.csv
Information 18/02/2009 22:04:42 Reader:{} Source File: C:\test_data_comma_delimited_small.csv
Information 18/02/2009 22:04:42 Reader:{} Read 4 Line(s)
Information 18/02/2009 22:04:42 Reader:{} Processed : 4 Record(s)
Information 18/02/2009 22:04:42 Reader:{} Rejected : 0 Record(s)
Information 18/02/2009 22:04:42 Reader:{} Records per second : 129.03
Information 18/02/2009 22:04:42 Reader:{} Time Taken : 00:00:00
Information 18/02/2009 22:04:42 Writer: {}, [Table=DEMO.dbo.U1] Inserted : 0 Record(s)
Information 18/02/2009 22:04:42 Writer: {}, [Table=DEMO.dbo.U1] Updated : 4 Record(s)
Information 18/02/2009 22:04:42 Writer: {}, [Table=DEMO.dbo.U1] Deleted : 0 Record(s)
Information 18/02/2009 22:04:42 Writer: {}, [Table=DEMO.dbo.U1] Errors : 0
Information 18/02/2009 22:04:42 Transformation Completed


John