Help!

Rather than go into a lengthy discussion about what I'm doing, I'll just preface this post with the fact that I'm calling a sproc that does nothing more than return a rowcount:

ALTER PROCEDURE [dbo].[usp_GetPendingBuilds]
@Out int OUTPUT
AS
BEGIN

SET NOCOUNT ON;

Select @Out = count(BuildStatus)
From tbl_PendingBuilds
Where BuildStatus = 99

END

I'm using ADO.Net as the connector, have an SSIS variable called "PendingBuilds" (INT32) and I'm mapping that parameter as follows:

Variable Name: USER::PendingBuilds
Direction: OUTPUT
DataType: INT32
ParameterName: @Out
ParameterSize: -1

It seems that no matter what I do, the variable "PendingBuilds" never changes. It took me 2 days just to get the step to execute successfully. Now that it does... the Variable sits there... teasingly, at "0"

Other than a lobotomy, what else should I be looking at?
Thanks for any valuable input!
-Ray

Ray Sotkiewicz

...this is crazy.. It can't be this difficult to set an Output variable in SSIS, can it? I've been at this for 3 solid days now, tried every connector, with every conceivable combination of parameters, with no luck.

The sproc return the desired rowcount of a table both as a single-row resultset, and as a return value. And still the Output param in the SSIS package remains untouched.

Help, before I become an Oracle convert! :-) At this point I'm even willing to let someone remote into this server to set me straight!



Ray Sotkiewicz

 

You're right, it's not that difficult ;)

Just to make sure that you've done the following:
Execute SQL Task Properties:
  ConnectionType: ADO.NET
  Connection: (Local).AdventureWorks
  SQLSourceType: Direct Input
  SQLStatement: usp_GetCount
  IsQueryStoredProcedure: True
Parameter Mapping:
  Variable Name: User::MyCount
  Direction: Output
  Data Type: Int32
  Parameter Name: @OutCount

And that's it. My variable gets the count of my procedure which looks like:
Create Procedure usp_GetCount
  @OutCount Int OUTPUT
As
Select @OutCount = Count(*) From HumanResources.Department

I must be doing something wrong then. I can't imagine what in the world it would be though.. Like I said.. tried every concievable angle with no joy.

What SP are you on?

I'm disappointed because this is such a seamingly simple task to perform in SSIS. As a Sr-level DBA I'm more disappointed in myself than anything... yet also --Extremely-- frustrated at yet another bloated, convoluted, buggy Microshoft product.

Ray Sotkiewicz

I'm using SP2 but I'm not sure if I was when I tested your scenario; maybe at that time I was on SP1.

On my side, I do not agree with your sentence as this product is buggy, I only discovered 1 bug within SSIS since I'm using it (1year an a half) and I'm very glad of the product they made; way much better than DTS.

I never did get this figured out. I suspect I may have to wipe the system. I'm on SP2.

I eventually replaced the DTS package I wanted to migrate to SQL2K5 with a stored procedure that does the job.

I'll have to try this on a fresh install.

Ray Sotkiewicz

Flysim, if you don't think the product is buggy, it isn't because you are doing anything different, you just got lucky somehow.

My configuration is exactly as is stated by both of you, but I am having the same problems as Ray, and I my SQLStatement correctly includes the parameters, unlike Flysim's, who doesn't show the parameter required by his sp.

Ray, did you have any better luck after the re-install?

The Value in the Variables window may or may not update when you execute an Execute SQL Task and push a value into the variable.

If your goal is to determine that the variable is in fact being updated, add a Script Task to the Control Flow, connect the Execute SQL Task to it using a Success Precedence Constraint, and double-click to edit.

In ReadOnlyVariables, add the name of the variable. Click the Design Script button and add code similar to the following:

Dim iMyVar as Integer = Convert.ToInt32(Dts.Variables("MyVar").Value)
MsgBox(iMyVar.ToString)

This script task will display a messagebox with the value of the Variable named "MyVar". If you are successfully pushing a value into the variable, you will see this number change.

One way to test this is to substitute the call to the stored procedure in the SQLStatement property of the Execute SQL Task with T-SQL similar to the following: Select 42. If you see 42 in the messagebox, the variable value is changing.

Hope this helps.

:{> Andy Leonard

Andy Leonard

Thanks for the response, Andy. I wasn't looking for the Variables window to be refreshed so much as trying to resolve the errors when trying to use output variables. It seems that it is throwing back errors, even when the configuration is set up properly, and I am just at a loss in what to do with that. I can get the data back in this instance using a result set, but that's not always possible and I need to be able to use output variables. Any ideas?

Hi,

   Most of the time I use ResultSets. I find parameters to be very finicky about data types and I usually don't have the kind of time on a project to finick. :)

:{> Andy

Andy Leonard