I have a folder with a .sql files; 1 file per query. I want to execute all queries/ sql files and save them as csv.
Is there a way to do that automated without using the windows cli (disabled in my environment). I do have the SQL Server Management Studio.
I would approach this task using SSIS, providing you have Business Intelligence Development Studio (BIDS) installed.
First create a 'Foreach Loop Container' pointed to the folder with the SQL files, then use a variable to retreive each file name.
Next, create a flat file connection and set the 'Connection String' property to the variable that contains the file location.
Next, using the 'Execute SQL Task' component set the 'SQLSourceType' to 'File Connection' and the 'FileConnection' to the one created in the previous step.
Finally, depending on how the data is returned you have a couple of options, if the result set is small, only a row or a single column, then you can save the results to a variable and using a 'Dataflow' task create a 'Derived Column' component and export the contents of that variable to a CSV file. Or, if the dataset is larger you could dump the results to a temp table and then using an 'OLE DB Source' and 'OLE DB Destination' you could push the full result set straight into a CSV.
Hopefully this isn't too convoluted of a solution, this approach has the advantage of being able be run from either a remote machine or from the server itself, plus you can automate its execution with a SQL Agent Job.
Create a VB.NET console application.
Generate a list of files that end in .SQL from the folder in question.
Load the contents of each file into individual SQL Commands
Execute the SQL Command for each, storing the results in DataSets.
For each table in each dataset, create a new .csv file
For each .csv file, you will need to iterate over each cell in the datatable, and utilize proper escaping for .csv files.
Use 'for' in combination with either sqlcmd or bcp command for each file in the script folder.
