Run SQL Script within a table/string and output results to file

Question

I need to run the SQL scripts that are held in a report configuration table and output the results as .csv or .xls files in a desired folder with a file name that is also specified within the report config table.

An example of the table is as per script below with column A being the desired file name to be created and Column B contains the script that will be executed.

Ideally I require a script that i can drop into a Stored Proc that will run down each row in the table and export and create each rows results as a separate file in a desired folder.

I think it needs to be a recursive query but I'm really not sure where to start, any advice or help is greatly appreciated.

EDIT: So it was suggested that I use SSIS, can this be done with the existing toolbox items or would I need to write a script to do this? There are hundreds of SQL scripts in the table so efficiency is key!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_Table_PS](
  [File_Name] [nvarchar](100) NULL,
  [Script_To_Run] [nvarchar](100) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) 
VALUES (N'Report_1', N'Select * FROM FRED')
GO

INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) 
VALUES (N'Report_2', N'Select * FROM BOB')
GO

INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) 
VALUES (N'Report_3', N'Select * FROM DAVE')
GO

Answer 1

You'd probably be best using a WHILE loop or a CURSOR for this operation. Although a WHILE loop requires an ID field. Something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_Table_PS](
  [File_Name] [nvarchar](100) NULL,
  [Script_To_Run] [nvarchar](100) NULL,
  [Report_ID] INT IDENTITY(1,1)
) ON [PRIMARY]
GO

INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) 
VALUES (N'Report_1', N'Select * FROM FRED')
GO

INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) 
VALUES (N'Report_2', N'Select * FROM BOB')
GO

INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) 
VALUES (N'Report_3', N'Select * FROM DAVE')
GO

DECLARE @SQL NVARCHAR(MAX)
DECLARE @SCRIPT NVARCHAR(MAX)
DECLARE @FILENAME NVARCHAR(50)
DECLARE @ID INT

SET @ID = 1

WHILE @ID <= (SELECT MAX(REPORT_ID) FROM [dbo].[Test_Table_PS])
    BEGIN
        SELECT @SCRIPT = SCRIPT_TO_RUN FROM [dbo].[Test_Table_PS] WHERE @ID = REPORT_ID
        SELECT @FILENAME = FILENAME FROM [dbo].[Test_Table_PS] WHERE @ID = REPORT_ID

        SET @SQL = 'BCP "'+@SCRIPT+'" QUERYOUT "'+@FILENAME+'" -T -c -t,'
        PRINT @SQL
        EXEC (@SQL)

        SET @ID = @ID + 1
    END

Don't forget to add the filepath to your filenames.