Executing multiple .sql files in folder

Question

Post reply

Executing multiple .sql files in folder

Ahmad Osama

SSCertifiable

Points: 7676
More actions
November 15, 2008 at 12:54 pm

#132091

Hi All,
How can I link Execute sql task to foreach loop container to run all *.sql file contained in a folder.
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 1

any body on this one.......

Regards,
[font="Verdana"]Sqlfrenzy[/font]

bhawna.chawla Newbie Points: 8 More actions · Answer 2

Create batch file with the following contents and replace

server-name, database-name, user-name and password.

@for %%s in (output\sql\*.sql) do ( sqlcmd -S server-name -d database-name -U user-name -P password -i "%%s" )

@sqlcmd -S server-name -d database-name -U user-name -P password -Q

@echo ---------------------

@echo Pausing for 5 secs...

arussell_10 SSCrazy Points: 2110 More actions · Answer 3

I think you can use a variable in the for loop that with get each file and then use that variable as an input parameter to the execute sql task and I think you specify the parameter in the sql task itself as a ?. Ex: select * from tbl where col1 = ? or something like that.

bhawna.chawla Newbie Points: 8 More actions · Answer 4

ups!! pasted from my batch directly:)...actually only first line is sufficient:

@for %%s in (output\sql\*.sql) do ( sqlcmd -S server-name -d database-name -U user-name -P password -i "%%s" )

😉

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 5

thanks bhawna......

Actualy I was looking for a solution in SSIS......

I created a ForEach loop container and a variable "GetFile" under a variable mappings in foreach loop container.

I then created an Execute SQL task and passed the variable "GetFile".

How ever am getting the error

[Execute SQL Task] Error: Executing the query "testssispkg.sql" failed with the following error: "Could not find stored procedure 'testssispkg.sql'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Raj's Ten Centuries Points: 1044 More actions · Answer 6

Hi,

Are you missing the path of your file 😉 ?

Raj

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 7

Rajesh (11/18/2008)
Hi,
Are you missing the path of your file 😉 ?
Raj

I don't think so......

As if I pass the full path in the variable it still gives the same error....

Regards,
[font="Verdana"]Sqlfrenzy[/font]

kanejeeves SSC-Addicted Points: 451 More actions · Answer 8

I think you're out of luck. I was disappointed when I saw that all MS provides for entering a SQL Statement is the squirrelly text box. It would be great if they included one more option in the SQLSourceType dropdown: File in project. That way all your sql can be accessible in the Misc folder instead of being buried in the squirrelly boxes.

arussell_10 SSCrazy Points: 2110 More actions · Answer 9

Do you have the database connection set up correctly? If so, are you sure you have access to execute the stored proc?