SQL Server Performance

SSIS invoking SP and getting output

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Jun 15, 2006.

  1. SQL_Guess New Member

    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 @PackageName=@PackageName, @PackageStatus=@Available OUTPUT ,@PackageUid=@PackageUid OUTPUT<br />IF @ReturnCode &lt;&gt; 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=':D' />BPackageNameInputVARCHAR1<br />User:<img src='/community/emoticons/emotion-4.gif' alt=':p' />ackageStatusOutputSHORT2<br />User:<img src='/community/emoticons/emotion-2.gif' alt=':D' />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=':D' />BPackageId, it appears as though that value is being put into the User:<img src='/community/emoticons/emotion-4.gif' alt=':p' />ackageStatus parameter - I noticed when 'watching' the execution that User:<img src='/community/emoticons/emotion-4.gif' alt=':p' />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::ReturnCodeReturnValueLONG@ReturnCode<br />User:<img src='/community/emoticons/emotion-2.gif' alt=':D' />BPackageNameInputVARCHAR@DBPackageName<br />User:<img src='/community/emoticons/emotion-4.gif' alt=':p' />ackageStatusOutputSHORT@PackageStatus<br />User:<img src='/community/emoticons/emotion-2.gif' alt=':D' />BPackageIdOutPutLONG@DBPackageId<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
  2. SQL_Guess New Member

    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

Share This Page