none
SSIS package to write various results to a text file RRS feed

  • Question

  • Hello,

    I have to create an SSIS package to write various results to a text file.  The first thing is to list the contents of a stored procedure.  I'm guessing that this would involve calling sp_helptext.  The next thing would be the results of the stored procedure which would be a few columns with headers.  What would be the best way to accomplish this?

    Thursday, September 12, 2019 6:08 PM

Answers

All replies

  • Hi there,

    It will require two calls to the stored proc, and yes, you can capture the procedure body with sp_help_text.

    So the 1st call you put into one file, similarly, the 2nd.

    And then you just append file #2 to file #1. This can be done using the Script Task that has some C# or VB code.

    https://stackoverflow.com/questions/5096194/c-appending-contents-of-one-text-file-to-another-text-file

    Or a DOS command (Execute Process Task):

    copy /b file1 + file2 file1


    Arthur

    MyBlog


    Twitter


    • Edited by ArthurZModerator Thursday, September 12, 2019 7:24 PM
    • Marked as answer by pdconway01 Thursday, September 19, 2019 12:23 PM
    Thursday, September 12, 2019 7:24 PM
    Moderator
  • Hi pdconway01,

    1. Use Execute SQL Task to run the stored procedure. Specify the ResultSet to be Full result set and identify the variable in Result Set.
    2. Connect a Data Flow Task to load the result from the variable to a flat file destination.
    3. In Data Flow Task, use Script Component as a source to acquire the result.

    Please refer to my screenshots below.

    The C# codes in Script Component are as following.

    public override void CreateNewOutputRows()
        {
            /*
              Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
              For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
            */
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, Variables.vResultSet);
            foreach (DataRow dr in dt.Rows)
            {
                Output0Buffer.AddRow();
                Output0Buffer.EmailAddress = dr["EmailAddress"].ToString();
                Output0Buffer.FirstName = dr["FirstName"].ToString();
                
            }
        }

    This is a great example. Please have a try.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 13, 2019 3:01 AM
  • Hi Zoe, I tried your example but I'm getting an error. I'm not seeing any output columns for the Script Transformation Editor and that is causing errors in my script component.
    Friday, September 13, 2019 3:54 PM
  • @pdconway01

    please post the errors


    Arthur

    MyBlog


    Twitter

    Friday, September 13, 2019 8:52 PM
    Moderator
  • Hello,

    I tried something a little different.  In a data flow task, I executed the stored procedure and  wrote the results to a flat file.  How would I go about writing the sp_helptext to a flat file as well?

    Monday, September 16, 2019 3:15 PM
  • Hello,

    I tried something a little different.  In a data flow task, I executed the stored procedure and  wrote the results to a flat file.  How would I go about writing the sp_helptext to a flat file as well?

    Hi pdconway01,

    It is very similar to what you already did. You would need SSIS Data Flow Task with OLEDB Source Adapter and Flat File Destination Adapter.

    Inside OLEDB Source Adapter  you would need to execute something along the following:

    sp_helptext 'dbo.YourStoredProcedureName';


    Monday, September 16, 2019 3:24 PM
  • I created a data flow task and an OLE DB Source with the following SQL command - sp_helptext
    'dbo.YourStoredProcedureName'  (using my actual stored proc name). 
    When I click Preview, I get an error:

    Exception from HRESULT: 0xC020204A
    Error at Data Flow Task 2 [OLE DB Source [16]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The metadata could not be determined because statement 'INSERT #CommentText VALUES
                    ( @LineId,
                      isnull(@Line, N'') + isnul' in procedure 'sp_helptext' uses a temp table.".

    Error at Data Flow Task 2 [OLE DB Source [16]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

    Monday, September 16, 2019 3:44 PM
  • Hi pdconway01,

    1. First, to test, please execute it in SSMS:
      sp_helptext 'dbo.YourStoredProcedureName';
    2. As an alternative, there is another way to get it.
      And again, test it first in SSMS. The 
      ROUTINE_DEFINITION column holds the sp body.

    SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'YourStoredProcedureName';



    Monday, September 16, 2019 4:32 PM
  • When I run sp_helptext in SSMS, I get the text for the stored proc, each line in its own row. When I run your alternative that is listed above in SSMS, I get:

    ROUTINE_SCHEMA  ROUTINE_NAME  ROUTINE_DEFINITION

    Monday, September 16, 2019 5:14 PM
  • When I run sp_helptext in SSMS, I get the text for the stored proc, each line in its own row. When I run your alternative that is listed above in SSMS, I get:

    ROUTINE_SCHEMA  ROUTINE_NAME  ROUTINE_DEFINITION

    USE YourDBname;

    GO

    SELECT * --ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES

    -- WHERE ROUTINE_NAME = 'YourStoredProcedureName';

    Just  run it without the WHERE clause, to see what is going on.

    It has 2 additional columns of interest: SPECIFIC_CATALOG (for database name), and SPECIFIC_SCHEMA


    Monday, September 16, 2019 5:16 PM
  • When I run that in SSMS, I see all the stored procedures in the database, including the one I want (name shows under the SPECIFIC_NAME column)
    Monday, September 16, 2019 5:25 PM
  • When I run that in SSMS, I see all the stored procedures in the database, including the one I want (name shows under the SPECIFIC_NAME column)

    Very good. That was the intent.

    Now just add the WHERE clause with your specific stored procedure name.

    • Marked as answer by pdconway01 Thursday, September 19, 2019 12:22 PM
    Monday, September 16, 2019 5:27 PM
  • Hi pdconway01,

    What's the latest?

    Did the proposed solution help you to resolve the issue?

    Wednesday, September 18, 2019 8:20 PM
  • Sorry for the delay, I was pulled to work on another project.  I was able to get this working using the ROUTINE_DEFINITION to get the text of the stored procedure since SP_helptext wasn't working for me.  I wrote the needed outputs to various text files and used a script task to combine them into one report text file.  Thanks everyone for your help!
    Thursday, September 19, 2019 12:28 PM