Loading data from SQL Server to redshift error

Highlighted
Six Stars

Loading data from SQL Server to redshift error

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 Smiley Tongueosition :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.

 

Employee

Re: Loading data from SQL Server to redshift error

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 :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now