How to log the output of SSIS Execute SQL Task

Question

I am new to SSIS.

I created SSIS Package using Execute SQL Task. I called a Stored Procedure. My stored procedure prints few messages like 'Insert Started' 'Update Started' 'Update Completed' but it does not return any result set.

How do I write the output of stored procedure to a log file in SSIS.

Kindly help me out I am struggling for the past two days.

I tried using DTExec like this

DTExec /f "C:\Users\Karthick\Desktop\SSIS\Package.dtsx">MyOutput.txt

But it prints only Execute SQL Task values not the Stored procedure print statements.

Gunn

Please tell me How do I write the output of stored procedure to a log file in SSIS – Gunn

Answer 1

SSIS is the kind of product that if you right click it enough it will eventually do something. But seriously folks, it reminds me of VB6 having a bad day…

This sounds like a simple thing to do but it requires a lot of right clicking and squirreling away little bits of code here and there. Here are the steps:

1. Declare one or more output parameters in your stored procedure (SPROC) to contain your status messages
2. In SSIS, modify your call to the SPROC to contain SSIS question mark "wildcards" (or whatever they call them)
3. Add a variable to SSIS
4. Map SPROC output parameter(s) to SSIS variable
5. Set up the logging provider in SSIS
6. Enable an event to trigger writing your status messages to the log
7. Create another task is SSIS to actually write the data to the log. I recommend using SQL but up to you

If you read the links above you will get the idea. Read on for a few details and editorial comments.

The place to start is with your SPROC by declaring one or more output parameters and setting them with your status messages.

SET @my_status = 'Insert Started'

Maybe you want to concatenate all your messages into one output parameter, or have multiple ones. Up to you.

Now, right click your SSIS Control Flow canvas and add a variable. Rinse and repeat if you have more SPROC output parameters.

Right click your SQL Task and edit your SQL call to look something like this:

EXEC myStoredProcedure ? OUTPUT

If you have input parameters those need to be accounted for here. The ? Is important as it represents a zero based numbered parameter that you have to map in order for SSIS to actually do anything with your status message. The design is reminiscent of Wordstar mail merges from back in the stone age of personal computing.

Go to Parameter mapping. Click Add and look for your variable. It will be something ugly like

User::my_status

If we all thought Parameter Name was the parameter name we would all be wrong. In this example you would put 0 (zero) here since this is obviously the zero-eth parameter in your SPROC.

OK, now you have set up a variable that you can log. But you're not done yet. You have to set up another Execute SQL Task to actually log this. I am not going to walk you through this dear reader but hopefully Aalam Rangi's excellent article is still there to give you everything you need to do this. In a nutshell you must a) set up the SSIS logging provider, b)Enable an event that will trigger writing your data to the log, c) write a SQL insert statement with a bunch more question marks in it that will actually write your data to the standard SQL table you want to log to. If you want to log to a file, this article will still be helpful. Here is the insert code which works perfectly for SSIS and Visual Studio 2015. Thanks Aalam!

INSERT INTO [dbo].[sysssislog]
([event]
,[computer]
,[operator]
,[ source]
,[sourceid]
,[executionid]
,[starttime]
,[endtime]
,[datacode]
,[databytes]
,[message])
VALUES
('*SSIS-OnVariableValueChanged' -- Custom event name
,? -- param 0
,? -- param 1
,? -- param 2
,? -- param 3
,? -- param 4
,? -- param 5
,? -- param 6
,0 -- Zero
,'' -- Blank string
,?) -- param 7

Happy right clicking!

Lueluebke

Answer 2

You can approach in this way,

• Log SSIS in a SQL Table
• Log your stored procedure in a table
• Create a view for see for example together the logtable
• and print this view with the SSIS in a file

Odele

Is there any simplest way to achieve this – Gunn

My question is how to log the stored procedure print statements to log file – Gunn

i understand your question, but there is no way to do it directly. you must do a trick like this – Odele

Answer 3

I guess you can not catch the stored procedure print messages through Execute SQL Task. To do that you need to use either Script Task or Script Component. Inside the script you can invoke your stored procedure and use the Event Handler InfoMessage to catch the messages from stored procedure.

Sample code below -

        public event SqlInfoMessageEventHandler InfoMessage;
        void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            using (StreamWriter writer = new StreamWriter(<your log file path>))
                 {
                  writer.Write(e.Message);
                 }
        }

        public void Main()
        {
         using (var conn = new SqlConnection(<your connection manager>))
         using (var command = new SqlCommand(<your sp name>, conn)
             {
             CommandType = CommandType.StoredProcedure
             })
         try
            {
             {
              ((SqlConnection)conn).InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
                conn.Open();
                command.ExecuteNonQuery();
             }
            }
         catch
            {
              throw;
            }
         finally
            {
              command.Dispose();
            }

         Dts.TaskResult = (int)ScriptResults.Success;
         }

Klee