I am trying to execute an sql text file within the execute sql task activity

Pasting the file contents directly in works fine - however I am trying to use a File variable content to read the contents of an SQL file and execute that

{FILE(Content|Y:\DQSRC\SQL\CRODS\CRODS_LOAD\1_drop_crods_load_db.sql)}

When I test this in the variable window it correctly shows the contents of the file but in the SQL output it shows an error

Error in task sql: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'File Y:\DQSRC\SQL\CRODS\CRODS_LOAD\1_drop_crods_load_db.sql does not exist'.

Because of version control etc - the propagate form a master file method is definitiely prefered - can this be done as I have attempted or another option ?

Thanks
Brett

Hi Brett,

the problem is that the File Variable cannot read a file on a network drive. It has no arguments for rights. What you can do is either:

1. Create a file read Task and then, in this SQL Task you use the output of the previous File read Task. Use this Variable then: {TASK(PrevTask,StdOut)} - remember to add a Credential to the file read Task

or

2. Use a local file path instead

or

3. Copy the file locally using the Copy files Task. Please remember to use a Credential then

---

Thanks for the prompt reply - and I understand the solution - I just get a message back from the read file step about unable to find specified directory to write output but there is no output directory setting for the read file task ? just a path from which to find the file to read.

Thanks
Brett.

Thanks for the prompt reply - and I understand the solution - I just get a message back from the read file step about unable to find specified directory to write output but there is no output directory setting for the read file task ? just a path from which to find the file to read.

Thanks
Brett.

Message from Read file about unable to write directory? Sounds like a strange error. Please paste the whole error here.

Maybe you are getting an error about not finding the file. Then you need to use a Credential if it is a network drive.

---