MSSQL file input to tMap

Highlighted
Six Stars

MSSQL file input to tMap

Hi everyone. I'm brand new to Talend, and am struggling to do the following.

 

The purpose of the job I am creating is to take data from a source MSSQL database and put the results into a different target MSSQL database.  

 

I have complex MSSQL (T-SQL) SELECT query with some complicated logic and joins.  Rather than using a whole bunch of tDBInput components and attempting to create the correct JOIN logic and transformations in a tMap, what I would like is to use a resource .sql file, execute it, and have the resulting schema available to me in a tMap, so that I can map the results to an output table in a different database.  Is this even possible?  

 

i.e.

 

resource file (SQL file) >>> resulting schema available as input to a tMap  >>> map to an output >>> output to target database

 

I have found varied threads in this community where people have asked similar questions, but none of the solutions have worked for me (some made no sense, or I didn't appear to have the option of the components mentioned).

 

Many thanks in advance

 

Steve

 

Twelve Stars

Re: MSSQL file input to tMap

you are on the good way but each project have it's own solution.
just create one job who read and load data into 2 sql db.(did you have db links between dbs).
then use context to guive your job the sql query and the output table name.
then create a job who read raw data (sql query) and call previeous job……
good luck

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: MSSQL file input to tMap

I'm not quite sure what you're suggesting there.

 

I have have got a little further with this.

 

I have used a tDBInput component and used my SQL query in the Query text box, and created the schema.

I have then fed this into a tMap.  Then created an output in the tMap and outputted all the fields in the input to the output as is.

Then I used a tDBOutput, connected it to my target DB, told it the table to target, synced the columns.

 

This is working and does achieve what I am after.

 

However, the one drawback is the input SQL statement is contained within the tDBInput component, instead of in a resource file which is what I want to do as this offers me better source control of the raw SQL.

 

job1.png

 

 

Twelve Stars

Re: MSSQL file input to tMap

are you on the open verssion? if yes, you did not have dynamic schema so you cannot globalise your job! so one job by table.
if you have dynamic data type you can use it. so you may have one job for all data transfer.

if you are on open and you want to use a configuration fie to store sql queries, your queries have to generate insert into query.
then you can execut insert queries(stored as string) into destination db.

are you on open studio or entreprise ?

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: MSSQL file input to tMap

I am on Enterprise
Twelve Stars

Re: MSSQL file input to tMap

so you can use dynamic schemas
tfileinput --> tflowtoiterate -iterat -> tDB -> dynamic schema -> tdb

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Seven Stars

Re: MSSQL file input to tMap

Hi,

 

Yes, it's possible.

 

tdbpinput --> tmap ---> tdboutput

 

First, create a select query with required columns and your logic to pull the data from source. Use the select query on the query window and do the necessary mappings in tmap to get the data in output.Capture.PNG

 

Thanks,

Prabuj

 

/ * Don't forget to give kudos * /

Six Stars

Re: MSSQL file input to tMap

@prabuj27  I have already done that, and got that working, as I mentioned above (see my screenshot).  This is not what my question is.  I want to be able to use a SQL file, which is in my resource file list instead of embedding the SQL statement in the tDBInput component.

Six Stars

Re: MSSQL file input to tMap

@fdenis Which tFileInput component do I use?  fFileInputRaw?  These are the choices I get

 

tFileInput.png

 

I am brand new to Talend, and although your reply appears to be helpful, I'm struggling to pick and configure the components.

 

I am using a "tFileInputRaw" component to import my .sql file

tFileInputRaw.png

 

The context resource file is the .sql file containing the SQL statement.

 

I then have a "main" to a "tFlowTolerate" component.  There doesn't appear to be any configuration options for this component.  How do I configure it to do what I want?

 

This component is then "iterate" to a "tDBRow", but the schema produced by the input file isn't anywhere, so how do I configure it?

 

Is there any chance you can expand your reply to explain exactly how I should set this up?

Twelve Stars

Re: MSSQL file input to tMap

delimited and change the end line \n by ;

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Twelve Stars

Re: MSSQL file input to tMap

but the best way is to use one query by file all file in a folder and add number in name to order them. destination file must be file name
the use tfileListe

or

one file with the list of sql file to run and the name of destination table

Francois Denis

Tag as "solved" for others! Kudos to thanks!

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

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