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.

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!