SSIS invoking SP and getting output

Question

Post reply

SSIS invoking SP and getting output

Regan Galbraith

SSCertifiable

Points: 5227
More actions
June 15, 2006 at 7:06 am

#114019

Hi all,
I'm having some fun tryhing to understand/use variables and parameter's and expressions with SSIS.
I have a Execute SQL Command on my Control Flow. It executes a SQL Stored Procedure. In T-SQL, I run this in the following way:
--code
--unit testing
SET NOCOUNT ON
DECLARE
@PackageNameVARCHAR(512),
@AvailableSMALLINT,
@PackageUidINT,
@ReturnCodeSMALLINT
SET @PackageName = 'IntegrationServicesController'
EXEC @ReturnCode = dbo.CheckSSISPackageStatus @PackageName=@PackageName, @PackageStatus=@Available OUTPUT ,@PackageUid=@PackageUid OUTPUT
IF @ReturnCode <> 0
PRINT 'An Error occurred : @ReturnCode = ['+str(@ReturnCode)+']'
ELSE
PRINT 'SUCCESS : @Available = ['+(cast(@Available as varchar(15)))+']'
SELECT @Available as [Package Status], @PackageUid as [Package Uid]
PRINT''
--result
SUCCESS : @Available = [1]
Package Status Package Uid
-------------- -----------
1 39
When trying to invoke this in SSIS, I have set up the following on the SQL Command Task:
Parameter Mappings
Variable NameDirectionData TypeParameter Name
User::ReturnCodeReturnValueLONG0
User:BPackageNameInputVARCHAR1
User:ackageStatusOutputSHORT2
User:BPackageIdOutPutLONG3
Result SetNONE
SQLSourceTypeDirect input
SQL StatementEXEC ?= dbo.CheckSSISPackageStatus ?, ? OUTPUT, ? OUTPUT
I tried to use parameter names, but I continuously got error message saying parameter unknown (iirc), so I went back to this. My implied understanding is that the numbers refer to the position of the variables?
The problem I have is that since I added the additional User:BPackageId, it appears as though that value is being put into the User:ackageStatus parameter - I noticed when 'watching' the execution that User:ackageStatus goes to 39.
Question 1 : why can I use variable names for the parameter mapping, like:
Parameter Mappings
Variable NameDirectionData TypeParameter Name
User::ReturnCodeReturnValueLONG@ReturnCode
User:BPackageNameInputVARCHAR@DBPackageName
User:ackageStatusOutputSHORT@PackageStatus
User:BPackageIdOutPutLONG@DBPackageId
Result SetNONE
SQLSourceTypeDirect input
SQL StatementEXEC @ReturnCode= dbo.CheckSSISPackageStatus @DBPackageName, @PackageStatus OUTPUT, @DBPackageId OUTPUT
Question 2 : Is there a better way? Am I doing something Stupid?
I am very new to SSIS - this is my first real SSIS package for production (I've made a few by following sample/tutorials, and played with 1 or 2 tasks). Unfortunately, the company I working for doesn't have anyone who has delivered anything in SSIS.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 1

some progress:

following my google sarch, I found this article/blog:

http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters

and after reading it, I changed my invocation from:

Connection Type: OLE DB

to

Connection Type: ADO.NET

This allows me to give real names to my parameter's, but more importantly I can actually get both values into the variables I expect them in, and expect the next SP. The walls are esafe for a little while longer!

PS - This forum does a LOT of formatting that causes post to go funny. the are actually [: D] (inside the []), the are actually [: P] (inside the [])... and forget about trying to show someone some XML tag's etc. -- is there a way I can get around this?