MSSQL file input to tMap

[Anonymous]

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

[fdenis]

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

[Anonymous]

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.

[fdenis]

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 ?

[Anonymous]

I am on Enterprise

[fdenis]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[fdenis]

delimited and change the end line \n by ;