Stored Procedure with Return Values in SSIS

For prospective students on the course “MS SQL Server Developer” Evgeny Turkestanov, teacher and database expert, has prepared a useful article.
We also invite you to an open webinar on the topic “Polybase: Life Before and After”. In the lesson, participants, along with an expert, will look at how it was possible to interact with other databases before Polybase, and how it works now.

With all my twenty years of experience with MS SQL Server and SSIS (once upon a time DTS), I never liked stored procedures with a return value. I don’t know why it happened. Maybe because “storage” more often had to be used to implement some logic or return a set of records, and to get one value – used functions. Well, that’s how it happened. I mean that this dislike is mutual, which was confirmed in the last project, where, for the life of me, I had to assign the return values of the procedure to variables with SSIS. Initially, the package was not mine, but another developer. I won’t say anything bad, everything was done competently enough.

The procedure returned two DATETIME values. In the process of work, I stepped on some rake, which prompted me to write this article.

So, given:

1. SSIS package

2. Package variables:

The package, of course, is a test one, but made by analogy. Sorry, but, unfortunately, I cannot show the original one.

3. There is a procedure that takes two dates from the table. The dates are needed to determine the intervals for the incremental filling of the database storage. The following is a simplified text of the procedure:

As you can see, there is nothing complicated here, everything is even too simple. It was possible, in principle, not to make return parameters, but simply return values. But, the owner is the master, that is, that is. If we run the procedure, the result will be like this:

Now our package.

Take the Execute SQL Command task, settings:

As you can see, the return result is not selected, since we have return parameters. We put our procedure in the SQL expression:

And now we assign parameters. Here’s the fun part.

For the returned parameters, select Output and package variables. The question is – what type of data for these parameters should we choose?

To test what will be returned, I created a task script that will display a C # MessageBox with the value of the variables. The settings and code are as follows:

Let’s return to our parameters, or rather, to their type. Which type should you choose? The returned procedure parameters are of type DATETIME. Let’s see what SSIS offers.

SSIS package "F:ProjectsSSISTestMultiplePutputTestMultiplePutputTestMultiplePutput.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query " exec dbo.testMultipleOutput ?, ? OUTPUT, ? OUTPUT" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at TestMultiplePutput: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "F:ProjectsSSISTestMultiplePutputTestMultiplePutputTestMultiplePutput.dtsx" finished: Failure.
The program '[50800] DtsDebugHost.exe: DTS' has exited with code 0 (0x0)

Interesting. Moreover, the error “crawls out” from the COM component – “problems with the request, the result or parameters are not configured.” But, as we have seen, the procedure works out. If you just copy the expression into SSMS, change the parameters and run, everything works. Honestly, the error made me lose some time, but I could not find a sane answer why this is happening. Perhaps there is a DATETIME to DATE conversion error. Moreover, this only happened if I used OUTPUT parameters in the component.

The package itself looks like this:

Everything is so simple that it is even boring. We look further.

I choose DBDATE, save, run. As you might expect, the date is back.

There is no point in choosing DBTIME, so let’s move on. I tried it though. The expected time has returned, but with today’s date. The same thing happened with DBTIME2. There are still a couple of types left.

DBTIMESTAMP. Someone told me that it is not worth working with this type in SSIS. Allegedly, it displays the date and time incorrectly, since it is not really DATETIME. We will see it now. Returned what was expected:

If we choose, for the sake of experiment, the last “temporary” type DBTIMESTAMPOFFSET, which, in principle, is designed to work with time zones, but what the hell is not kidding, it will return the correct date, but a different time:

There is another option for working with return parameters, but this is, say, for an amateur or for those who, like me, do not like them much. In Execute SQL Command, you can write a T-SQL expression in the text, something like this:

And customize the returned result

And define variables to assign values

As they say, it’s a matter of taste. There is no difference in performance, in style and in the solution itself, even with all my dislike for the returned parameters in the package, I am more impressed by the first option.