topic Re: MSSQL file input to tMap in Talend Studio

MSSQL file input to tMap

Anonymous — Wed, 05 Jun 2019 10:46:25 GMT

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

Re: MSSQL file input to tMap

fdenis — Wed, 05 Jun 2019 10:55:17 GMT

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

Re: MSSQL file input to tMap

Anonymous — Wed, 05 Jun 2019 13:15:16 GMT

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.

Re: MSSQL file input to tMap

fdenis — Wed, 05 Jun 2019 13:31:21 GMT

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 ?

Re: MSSQL file input to tMap

Anonymous — Fri, 07 Jun 2019 15:04:48 GMT

I am on Enterprise

Re: MSSQL file input to tMap

fdenis — Tue, 11 Jun 2019 08:49:51 GMT

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

Re: MSSQL file input to tMap

Anonymous — Tue, 11 Jun 2019 10:11:20 GMT

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.

Thanks,

Prabuj

/ * Don't forget to give kudos * /

Re: MSSQL file input to tMap

Anonymous — Tue, 11 Jun 2019 10:25:34 GMT

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

Re: MSSQL file input to tMap

Anonymous — Tue, 11 Jun 2019 10:42:43 GMT

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

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

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?

Re: MSSQL file input to tMap

fdenis — Tue, 11 Jun 2019 10:57:57 GMT

delimited and change the end line \n by ;

Re: MSSQL file input to tMap

fdenis — Tue, 11 Jun 2019 11:00:31 GMT

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