Query String Passed as Context Param is not running Properly

Highlighted
Five Stars

Query String Passed as Context Param is not running Properly

Hi Team,

 

I have a situation in which the query to be executed itself would be passed as a context parameter from the parent job.

The query string is approximately 1250 characters. When i try to print the query - am able to print them all (no truncation of character), but when i pass that as parameter and execute in a tmssql component, it is showing a column as Invalid.

 

Example : Query String (not real query though)- SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P from dbo.Table (of length 1250).

Issue - The error message is tMSSqlInput_1 Invalid column name 'K'. If i remove column 'K' the error says Invalid Column 'L'. But when i pass 'SELECT * FROM dbo.table' or 'SELECT A,B,C,D,E,F,G,H,I,J from dbo.Table' it is running fine. I checked tens of times all the columns i mentioned here are available in Table.

 

Someone, please help me to unveil this issue.

 

Highlighted
Ten Stars

Re: Query String Passed as Context Param is not running Properly

Does it work when you run it directly on the MSSQL? double checking MS SQL engine, skip Talend ...
Looks weird indeed!

Highlighted
Five Stars

Re: Query String Passed as Context Param is not running Properly

Yes it does execute in MS studio. I figured it out after 4 hours of head scratching and 8 cups of tea. It may sound silly, but that's the truth. Since I am passing the entire query as parameter I used dynamic column propagation and that is why when I use "Select * from table" it ran. But when I give columns A,B,C etc I am dropping some columns from table which I don't need. But i belive this dynamic option try to match table structure with the query we give. If it's * it's matching with it and ran, but when we select particular columns, it don't match with it so showing weird error. We did a workaround to handle this. But someone from Talend team may have to confirm this behaviour though.
Anyway, thank you ! Peace be with you !

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog