SQL Server Performance Forum – Threads Archive
SSIS invoking SP and getting output
Hi all,<br /><br />I’m having some fun tryhing to understand/use variables and parameter’s and expressions with SSIS.<br /><br />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:<br /><br />–code<br />–unit testing<br />SET NOCOUNT ON<br /><br />DECLARE <br />@PackageNameVARCHAR(512),<br />@AvailableSMALLINT,<br />@PackageUidINT,<br />@ReturnCodeSMALLINT<br /><br />SET @PackageName = ‘IntegrationServicesController'<br /><br />EXEC @ReturnCode = dbo.CheckSSISPackageStatus @[email protected], @[email protected] OUTPUT ,@[email protected] OUTPUT<br />IF @ReturnCode <> 0<br />PRINT ‘An Error occurred : @ReturnCode = [‘+str(@ReturnCode)+’]'<br />ELSE<br />PRINT ‘SUCCESS : @Available = [‘+(cast(@Available as varchar(15)))+’]'<br />SELECT @Available as [Package Status], @PackageUid as [Package Uid]<br />PRINT”<br />–result<br />SUCCESS : @Available = [1]<br />Package Status Package Uid<br />————– ———–<br />1 39<br /><br />When trying to invoke this in SSIS, I have set up the following on the SQL Command Task:<br /><br />Parameter Mappings<br />Variable NameDirectionData TypeParameter Name<br />User::ReturnCodeReturnValueLONG0<br />User:<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />BPackageNameInputVARCHAR1<br />User:<img src=’/community/emoticons/emotion-4.gif’ alt=’
‘ />ackageStatusOutputSHORT2<br />User:<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />BPackageIdOutPutLONG3<br /><br />Result SetNONE<br />SQLSourceTypeDirect input<br />SQL StatementEXEC ?= dbo.CheckSSISPackageStatus ?, ? OUTPUT, ? OUTPUT<br /><br />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?<br /><br />The problem I have is that since I added the additional User:<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />BPackageId, it appears as though that value is being put into the User:<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />ackageStatus parameter – I noticed when ‘watching’ the execution that User:<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />ackageStatus goes to 39.<br /><br />Question 1 : why can I use variable names for the parameter mapping, like:<br /><br />Parameter Mappings<br />Variable NameDirectionData TypeParameter Name<br />User::[email protected]<br />User:<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />[email protected]<br />User:<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />[email protected]<br />User:<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />[email protected]<br /><br />Result SetNONE<br />SQLSourceTypeDirect input<br />SQL StatementEXEC @ReturnCode= dbo.CheckSSISPackageStatus @DBPackageName, @PackageStatus OUTPUT, @DBPackageId OUTPUT<br /><br />Question 2 : Is there a better way? Am I doing something Stupid?<br /><br />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.<br /><br />Panic, Chaos, Disorder … my work here is done –unknown
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!
Panic, Chaos, Disorder … my work here is done –unknown
]]>