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.
我有一个带.sql文件的文件夹;每个查询1个文件。我想执行所有查询/ sql文件并将它们保存为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.
有没有办法在不使用Windows cli的情况下实现自动化（在我的环境中禁用）。我有SQL Server Management Studio。
3 个解决方案
#1
3
I would approach this task using SSIS, providing you have Business Intelligence Development Studio (BIDS) installed.
如果安装了Business Intelligence Development Studio（BIDS），我将使用SSIS来完成此任务。
First create a 'Foreach Loop Container' pointed to the folder with the SQL files, then use a variable to retreive each file name.
首先创建一个指向带有SQL文件的文件夹的“Foreach循环容器”，然后使用变量来检索每个文件名。
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.
接下来，使用“执行SQL任务”组件将“SQLSourceType”设置为“文件连接”，将“FileConnection”设置为上一步中创建的“文件连接”。
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.
最后，根据数据的返回方式，您有几个选项，如果结果集很小，只有一行或一列，那么您可以将结果保存到变量并使用“数据流”任务创建一个'派生列'组件并将该变量的内容导出到CSV文件。或者，如果数据集较大，您可以将结果转储到临时表，然后使用“OLE DB源”和“OLE DB目标”，您可以将完整的结果集直接推送到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.
希望这不是一个解决方案，这种方法的优点是可以从远程计算机或服务器本身运行，而且您可以使用SQL代理作业自动执行。
#2
2
Create a VB.NET console application.
创建一个VB.NET控制台应用程序。
Generate a list of files that end in .SQL from the folder in question.
从相关文件夹生成以.SQL结尾的文件列表。
Load the contents of each file into individual SQL Commands
将每个文件的内容加载到单独的SQL命令中
Execute the SQL Command for each, storing the results in DataSets.
为每个执行SQL命令，将结果存储在DataSet中。
For each table in each dataset, create a new .csv file
对于每个数据集中的每个表，创建一个新的.csv文件
For each .csv file, you will need to iterate over each cell in the datatable, and utilize proper escaping for .csv files.
对于每个.csv文件，您将需要遍历数据表中的每个单元格，并使用适当的转义为.csv文件。
#3
0
Use 'for' in combination with either sqlcmd or bcp command for each file in the script folder.
对脚本文件夹中的每个文件使用'for'与sqlcmd或bcp命令结合使用。
#1
3
I would approach this task using SSIS, providing you have Business Intelligence Development Studio (BIDS) installed.
如果安装了Business Intelligence Development Studio（BIDS），我将使用SSIS来完成此任务。
First create a 'Foreach Loop Container' pointed to the folder with the SQL files, then use a variable to retreive each file name.
首先创建一个指向带有SQL文件的文件夹的“Foreach循环容器”，然后使用变量来检索每个文件名。
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.
接下来，使用“执行SQL任务”组件将“SQLSourceType”设置为“文件连接”，将“FileConnection”设置为上一步中创建的“文件连接”。
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.
最后，根据数据的返回方式，您有几个选项，如果结果集很小，只有一行或一列，那么您可以将结果保存到变量并使用“数据流”任务创建一个'派生列'组件并将该变量的内容导出到CSV文件。或者，如果数据集较大，您可以将结果转储到临时表，然后使用“OLE DB源”和“OLE DB目标”，您可以将完整的结果集直接推送到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.
希望这不是一个解决方案，这种方法的优点是可以从远程计算机或服务器本身运行，而且您可以使用SQL代理作业自动执行。
#2
2
Create a VB.NET console application.
创建一个VB.NET控制台应用程序。
Generate a list of files that end in .SQL from the folder in question.
从相关文件夹生成以.SQL结尾的文件列表。
Load the contents of each file into individual SQL Commands
将每个文件的内容加载到单独的SQL命令中
Execute the SQL Command for each, storing the results in DataSets.
为每个执行SQL命令，将结果存储在DataSet中。
For each table in each dataset, create a new .csv file
对于每个数据集中的每个表，创建一个新的.csv文件
For each .csv file, you will need to iterate over each cell in the datatable, and utilize proper escaping for .csv files.
对于每个.csv文件，您将需要遍历数据表中的每个单元格，并使用适当的转义为.csv文件。
#3
0
Use 'for' in combination with either sqlcmd or bcp command for each file in the script folder.
对脚本文件夹中的每个文件使用'for'与sqlcmd或bcp命令结合使用。