Loading data from SQL Server to redshift error

desanip — Sat, 16 Nov 2024 05:13:08 GMT

Hello Talendians,

I have a simple talend job which extracts data from a sql server table and loads into redshift table. I am loading the records into a csv file and then to s3 before finally inserting into redshift table. My job is running into errors because there are 2 additional columns in my redshift table..i.e, Mobile_phone and zip_Code that are not present in source. These columns are nullable in my target. How can i overcome my problem?

Source Columns: site_id,
id,
contest_id,
member_game_id,
member_guid,
firstname,
lastname,
email,
birthdate,
accept_terms,
email_opt_in,
created,
modified.

Target:

site_id integer not null
id integer not null distkey,
contest_id integer not null,
member_game_id integer not null,
member_guid char(20) not null,
firstname varchar(255) not null,
lastname varchar(255) not null,
email varchar(255) default ''::character varying not null,
mobile_phone varchar(30),
zip_code varchar(50),
birthdate date,
accept_terms smallint default 1::smallint,
email_opt_in smallint default 0::smallint not null,
created timestamp,
modified timestamp,
etl_batch_id integer not null

The error is as below:

colname: type :col_length osition :raw_line :err_reason --->
accept_terms ,int2 ,0 ,133,"19"~"48253"~"20"~"2166790"~"19-10904087 "~"Gabrielle"~"Dean"~"gabrielletsmart@netscape.net"~"1981-11-06 00:00:00"~"1"~"0"~"2019-07-15 11:48:07"~"2019-07-15 11:48:07"~"164177" , Error Detail: "Invalid digit, Value '-', Pos 4, Type: Short "

I am attaching the data flow in the below screenshot. I believe i am missing an additional parameter and thats causing the issue.

Please help me with the issue.

Thanks.

Re: Loading data from SQL Server to redshift error

Anonymous — Sun, 21 Jul 2019 16:18:36 GMT

Hi,

The error says that you are trying to load an invalid value to integer.

" Error Detail: "Invalid digit, Value '-', Pos 4, Type: Short "

Could you please print the value using a tLogrow and see whether you have mapped the columns correctly?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

topic Re: Loading data from SQL Server to redshift error in Talend Studio

Loading data from SQL Server to redshift error

Re: Loading data from SQL Server to redshift error