DSXchange

Hi,

I am trying to load data in my Teradata table using a Teradata Connector stage.
I have used the User-Defined SQL functionality available there, I am using a File containing all the Insert SQLs.

Now I have used parameters in that SQL and defined those parameters in the job properties of the job. But the job aborts with the below message when I run the job
This parameter jpINPUT_SOURCE_DB is defined in the job properties, I am inputting the correct value at the run time as well. Not sure what I am missing. Kindly suggest.

I don't believe that it's possible to use parameter references successfully in an SQL file.

It's not, as you're finding there's no "second step" after the file is brought into the job to resolve any parameters. When I had a need to do exactly what you are doing, I had a pre-processing step where we took our "template" SQL file (with our own markers for where the runtime values needed to be) and edited in the values for the current run and had the job use that "runtime" version of the file.

Are you sure you really need to use a SQL File for this, that it is an appropriate solution?

our team routinely generates sql this way:
a user variable stage contains the parameterized sql
a flat file stage writes a 'this run' version of populated sql to a file on the server
the connector stage picks up the 'this run' file and uses that.

you can use a format of "statement" : '\n' to make the output file human readable.

there is probably a way to have the parameterized sql in a file and populated as well, but we chose to store the sql in the stage to save clutter and to it will migrate with the job without having to remember to drag a text file along with it every time.

Right. However, this parameter doesn't look like something that would ever change once the jobs were in their environment, hence my last question. Seems like it may come down to what "all the insert SQLs" means.

chulett wrote:Are you sure you really need to use a SQL File for this, that it is an appropriate solution?

This was our first approach for loading this particular table, since the INSERT SQLs were not finalized so we thought to have a file containing all the SQLs. In case, those needed to be changed, we'll simply update the file instead of changing the DS job.

UCDI wrote:a user variable stage contains the parameterized sql

Are you talking about the user variable stage available in the Sequence Job, we can't use sequences.

chandra.shekhar@tcs.com wrote:, we can't use sequences.

Is this for a technical or a political reason?

haha..
Actually, our client has made this rule of not using the Sequences.

Which would translate to "political".

no sequences is like saying don't use datastage. You lose at least 1/2 of the functionality of the tool.

Sounds like the client needs a wee bit of education!

I would wager it is one of those "Parallel Only!" sites where, for whatever reason, that is the mandate coming down from above. And it typically translates to no Sequence or Server jobs of any kind, no matter how appropriate one might be.