Conditional migration daily basis

One Star

Conditional migration daily basis


I am new to Talend. I have a job which runs once in a day. My input database is mssql server and output database mysql and whenever this job runs it should transfer only the difference.(e.g it should migrate only newly added row in mssql to mysql).
I have tried to create a log table in mssql and during migration input component will read the log table and transfer the rows those have id>last log id
(PFA job design image)
But here I am not able to store last inserted id in mssql server log table. Please guide me what will be the best way to design this job.

Input Component mssql query

select .Subject_ID, SB_Desc,Level_ID from Subjects,migration_log1 where tblSubjects.Subject_ID>migration_log1.log_id

Re: Conditional migration daily basis

where are you maintaining your control table in the job? try using a subjobOK to another input that selects the MAX(log_id) from your Subjects table and inserts it to your control table.
One Star

Re: Conditional migration daily basis

Thanks John,

My control table in mssql server (input component). Can you please guide me how to map MAX(subject_id) with the log_id column in output component.
PFA screen and help me

One Star

Re: Conditional migration daily basis

Have you tried changing the properties of Mysql input component to "Update or Insert" instead of just insert?
One Star

Re: Conditional migration daily basis


Job only have one Mysql out with and also 2 mssql input and I can set "Update or Insert" only for output component not for input possible for input component.
I have tried "Update or Insert" in both mssql output component but I am getting exception

Exception in component tMSSqlOutput_2
java.lang.RuntimeException: For update, Schema must have a key
at etl_jgate.ietl_subject_1_0_1.ETL_Subject_1.tMSSqlInput_2Process(

Please help for how to do conditional migration from periodic job

Community Manager

Re: Conditional migration daily basis

For update, Schema must have a key

For update or delete, you must set one or more column as a key on schema.

Best regards
Talend | Data Agility for Modern Business


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release


Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend