Automatically create rowid from import excel to SQL Server

Dear reader,

I am loading an excel-file with thousands of records. After transformation/validation, my goals is to write the finalized data to a SQL Server table. The excel-file however has no RowID's, so I would like to add a RowID to each excel-record/line.

Can someone guide me how I could solve this challenge?

regards,
Norman Veger

Hi Norman

Please use the Sequence transformation function

www.etl-tools.com/wiki/advanced-etl-proc...ns/numbers/sequence/



Thank you

Hi,

thnaks for the swift reply.

I am evaluating your product (ETL Processor) and trying to get these records from excel into an SQLExpress database (on my local machine). I have a valid and well tested connection to my SQLExpress instance (with SA credentials), I have a table to drop the data in, the mapping of the data is done and is giving me correct mappings, but when I execute the entire package, I get errors on each row that i am trying to insert into the table. The message I get for each line (11000 lines in excel):

Information 11-10-2022 11:06:27 Reader:{Reader} Data Source has: 11452 Record(s)
Error 11-10-2022 11:06:27 1 Record/Line:0 Record Rejected: Field: ID Value: Datatype: CHAR Message: Access violation at address 0000000000000000 in module 'AdvancedETLEnt.exe'. Execution of address 0000000000000000

Any idea why I can't load the data into the SQLExpress DB?

Any help would be welcome!

This kind of error usually happens when the old versions of MS SQL Server ODBC driveris being used

The easiest way to sort it out is to install the latest version of MS SQL Server Management studio
learn.microsoft.com/en-us/sql/ssms/downl...iew=sql-server-ver16
Or you can follow this guide
www.etl-tools.com/wiki/knowledgebase/tip...server-odbc-drivers/

Please keep us posted on your progress

Hi,

I am using the free version SQLExpress (not the full paid SQL Server). The SQL Server version installed is:

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19044: )

I have no idea why this goes wrong, any other directions?

We use SQL Server express here as well
Data is loaded into SQL server using MS SQL server ODBC drivers.
Old SQL Server ODBC Drivers had bugs which Microsoft fixed
Installing MS SQL Server Management Studio will install the latest ODBC drivers (it is free)

Here are the screenshots of my ODBC drivers



There is no doubt that our software may have bugs but I have seen so many times people using old drivers and complaining on our forum.