September 21, 2015 at 2:55 am
Hi,
I have been given a request at work that requires us 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.
Any advice or help is greatly appreciated.
Thanks in advance
------------------------------------------------------------------------------------------
-----SAMPLE TABLE SCRIPT -----------------------------------------------------------
------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[Test_Table_PS] Script Date: 21/09/2015 09:14:57 ******/
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 ''FRED''')
GO
INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) VALUES (N'Report_2', N'Select ''BOB''')
GO
INSERT [dbo].[Test_Table_PS] ([File_Name], [Script_To_Run]) VALUES (N'Report_3', N'Select ''DAVE''')
GO
September 21, 2015 at 3:42 am
Are you familiar with SSIS (Integration Services)? If so, it might be worth creating a package to do this. If not, it's fairly simple to loop through your table and use sqlcmd to run the scripts one by one and direct the output to the specified location.
John
September 21, 2015 at 4:01 am
I'm no expert with SSIS but i know my way around. which would be the best item from the toolbox list to use to get me started?
Regards
September 21, 2015 at 4:21 am
Use an Execute SQL task to get the list of reports, then loop through the list and use an Execute Process task to run each script.
John
September 21, 2015 at 7:25 am
John Mitchell-245523 (9/21/2015)
Use an Execute SQL task to get the list of reports, then loop through the list and use an Execute Process task to run each script.John
John, did you decide that an EP task was better than another ExecuteSQL task because of resultset concerns?
Another approach would be to set up a dataflow to read the reports dataset and then use an OLEDB Command to fire the SQL, for each row. At least I think that would work.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2015 at 7:49 am
Phil
I think it would be simpler. With Execute Process, you can use sqlcmd to execute the script from wherever it is. With another Execute SQL, you'd need somehow to pull in the contents of the script file before you can execute it. (Unless there's an option in Execute SQL to specify a file as opposed to in-line SQL or from a variable? If there is, I've never noticed or needed to use it!)
John
September 21, 2015 at 8:01 am
John Mitchell-245523 (9/21/2015)
PhilI think it would be simpler. With Execute Process, you can use sqlcmd to execute the script from wherever it is. With another Execute SQL, you'd need somehow to pull in the contents of the script file before you can execute it. (Unless there's an option in Execute SQL to specify a file as opposed to in-line SQL or from a variable? If there is, I've never noticed or needed to use it!)
John
When I read the post, I assumed that column B ("script to run") contained SQL statements and not files.
Column A is a list of file names, but these are the names of the files to which the results of running column B should be sent.
From memory, the results of running SQLCMD -o are not very pretty ... not standard CSV format, anyway.
Having said all of this, I'm starting to agree with you about using SQLCMD. Not knowing the structure of the output file in advance makes SSIS solutions challenging.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2015 at 8:09 am
I imagine SSIS has come on a lot over the years, we built "Tools" years ago that have served us well so I haven't had need to use it, as it has matured.
Years ago we built a simple little command line tool which takes parameters for Database name + SProc name, Filename and delimiter "style" (CSV or TAB) and runs the SProc and generates the output file. It does a proper job of CSV - embedded Comma and Quotes and all that.
We schedule our EXE either from SQL Agent (e.g. using a table of SProcs names and target filenames) or Windows Scheduler etc.
If I was doing this again today would it be a POP to do in SSIS?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply