June 15, 2006 at 7:06 am
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.
June 15, 2006 at 7:49 am
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?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy