locked
Running multiple scripts from a folder and generating multiple CSV files RRS feed

  • Question

  • I have multiple Script files(.sql files) which need to be run one after the other and generate CSV files with the data of each script file and the CSV's should have the same name as assosciated script file.

    As of now I am manually executing each file and saving results as CSV by right-click ing on the data which is a tedious process as there are around 200 scripts which are to be run.

    so I am looking for a automation of process.  Is there a way this can be done using TSQL or SSIS?

    • Moved by Naomi N Monday, September 24, 2012 8:42 PM SSIS (From:Transact-SQL)
    Monday, September 24, 2012 8:33 PM

Answers

  • I think it should be relatively easy in SSIS, so I move this thread there. This may be a bit of overkill for this particular problem, but perhaps can help

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/scripting-all-the-jobs-on-your-sql-serve


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by sk12346 Thursday, September 27, 2012 3:37 PM
    Monday, September 24, 2012 8:41 PM
  • You wouldn't need C# in your scneario. Assuming that the csv files to be generated are of the same format,

    1) Use a foreach container and enumerate over the .sql script files to execute your sql scripts one by one. Check this link where Jamie has given a video walkthrough on how to do it

    2) Within the foreach container, you can either have your sql scripts write into a temporary sql table and then use a data flow task to export the data from this temporary sql table to a csv file

    or

    If the .sql file contains only select queries you can set the sql query of a ole db source from a variable and then export the data to a csv file. (Check this for reference)


    http://btsbee.wordpress.com/

    • Marked as answer by sk12346 Thursday, September 27, 2012 3:37 PM
    Monday, September 24, 2012 10:44 PM

All replies

  • I think it should be relatively easy in SSIS, so I move this thread there. This may be a bit of overkill for this particular problem, but perhaps can help

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/scripting-all-the-jobs-on-your-sql-serve


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by sk12346 Thursday, September 27, 2012 3:37 PM
    Monday, September 24, 2012 8:41 PM
  • Hi,

    I can understand the script part and make changes according to my needs but I amm not used to C# nor I used C# with SSIS before. Can you give me a outline of how to execute the code in the article from SSIS?

    Thanks for your time and help.

    Monday, September 24, 2012 8:59 PM
  • You wouldn't need C# in your scneario. Assuming that the csv files to be generated are of the same format,

    1) Use a foreach container and enumerate over the .sql script files to execute your sql scripts one by one. Check this link where Jamie has given a video walkthrough on how to do it

    2) Within the foreach container, you can either have your sql scripts write into a temporary sql table and then use a data flow task to export the data from this temporary sql table to a csv file

    or

    If the .sql file contains only select queries you can set the sql query of a ole db source from a variable and then export the data to a csv file. (Check this for reference)


    http://btsbee.wordpress.com/

    • Marked as answer by sk12346 Thursday, September 27, 2012 3:37 PM
    Monday, September 24, 2012 10:44 PM
  • Thanks btsbee. It helps.  I also want to know about the other way of executing C# code in SSIS. can you give me a few steps on how to use C# code in SSIS.

    Thanks

    Thursday, September 27, 2012 3:36 PM
  • Great!

    Well if you want to use C# code in SSIS you use a Script Task (check this) which supports both C# and VB scripting. The general idea is to use out of the box SSIS tasks and transformations as far as possible and things that cannot be achieved/ slower using these objects write a custom script.


    http://btsbee.wordpress.com/

    Thursday, September 27, 2012 3:48 PM
  • @btsbee: I followed the steps in the video but the process ended in error:

    [Execute SQL Task] Error: Executing the query "select code,description, (replace(replace(inactive..." failed with the following error: "Invalid object name 'audiometricprotection'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Also how to set up the result set in Execute SQL Task to a temporary table? All the SQL files generate different table structures.Can the result set be populated into CSV? Thanks

    • Edited by sk12346 Wednesday, October 3, 2012 4:33 PM
    Wednesday, October 3, 2012 4:32 PM