Execute row SQL script can't read .sql file. I am getting this error

Question

2018/02/01 11:20:00 - Execute row SQL script.0 - We can not find field [C:\Users\abc\Documents\xyz\query\record.sql] in input stream!

My steps involve -

1. get variables - input
2. Execute row SQL script - where I am trying to read my query file
3. text file output

In Execute row SQL script I have given C:\Users\abc\Documents\xyz\query\record.sql in SQL fieldname.

EDIT

Clarifying, I want the job to read query (from files) and give a text output all by itself (run and output the select query result).

Answer 1

I'm copying/pasting the explanation from the docs of two config options from the Execute Row SQL Script step:

SQL field name: The field that either contains the SQL to execute or optionally specifies the path to a file that contains SQL to execute

and

Read SQL from file: If checked, then the SQL field name option specifies a file that contains SQL to execute, otherwise the SQL field name option specifies the actual SQL to execute. (Available since 5.0)

So you can't specify a file path in the SQL field name option, but you can specify a field from the stream that contains a file path to the desired sql. Otherwise, you should define in this option a field that contains the sql text.

Hope it helps.

EDIT 1

As you need to execute a select query from a file, I would recommend the step Dynamic SQL row, just as the following image.

I think it will do the job.

EDIT 2

The following just do the dynamic sql execution. First, you need to load the sql queries into the stream. After that, a job will be executed for each sql. In this job, a variable containing the sql query is set and a transformation is executed next. This transformation basically is a Input Table step that fills dynamically the sql box with the previous set sql variable.

Now I think it's what you're looking for. Hope it helps.