Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Highlighted
Four Stars

Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Hi Everybody,

I'm very new to TOS and I have almost a similar situation in MS SQL where i'm suppose to insert latest records to a table AC. So I'm trying to get max Date from AC to know which records to fetch from another SQL DB. For this as per above diagram i have started with a tMSSQLinput_1 component with above mention query then i want to store the result set of  tMSSQLinput_1 to tSetGlobalVar_2 (not sure if this is the best approach). As you can see i have added a Key and just to test if the results are stored i have used tLogRow_1 Component. but i am getting this error

"Execution failed : object is not an instance of declaring class
Job AgentCubeNew ended at 20:59 06/03/2019. [exit code=0]"

I would like to know is this the right approach?, also where should i write globalMap.put("myValue",*value*) to set the value in my variable from the query ?

Once i'm able to set the variable i would like to call it in another MSSQLinput component to get records from another db table which > max(date) of table AC

Please guide

my talend is Version: 5.6.1

Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit,you can do multiple one is creating context and loading that max date into context and reading that context variable in the next component where you were writing query based on date.

 

and another way is set using tsetGlobalvar. on which component are you getting an error?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Four Stars

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@manodwhb
can you see the screen shot and guide me accordingly.
Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit ,i see the problem in tSetGlobalVar,value should be like row1.colum with out " ". can you provide that way.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Four Stars

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@manodwhb
i have replaced tsetGlobalvar_2 value to row1.maxdate
still the same error
"Execution failed : object is not an instance of declaring class
Job AgentCubeNew ended at 10:29 07/03/2019. [exit code=0]
"
Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit ,on which component are you getting that error?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit ,check the below example,

Untitled.pngUntitled.pngUntitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Four Stars

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@manodwhb Please see the attached
Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit ,I relay not able to test your issue. we have dig into one step down,with out tSetGlobalvar was that job running successfully just to keep toracleinput and tlogrow

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Four Stars

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

i think its because of the format of datetime can you try with "yyyy-MM-dd  HH:mm:ss"

Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit ,i am not getting in error in talend Open studio 7.1.1,check below

Untitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Four Stars

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@manodwhb 

i tried it without tGlobalVariable still the same Class Cast exception. i even tried changing the date format by
"Select format(max(Date), 'dd-MM-yyyy') as mdt from AgentCube" same error. when i guess schema after this updated query the system reads it as string with 400 length

Highlighted
Moderator

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@abdulbasit ,can you check the schema and query of the tOracleInput.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Four Stars

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

@manodwhb
its tMSSQL input
the query is mention in the 1st screenshot

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog