Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9
This is the forum to discuss the Wrox book Professional SQL Server 2005 Integration Services by Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson, Kathi Kellenberger, Andy Leonard, Erik Veerman, Jason Gerard, Haidong Ji, Mike Murphy; ISBN: 9780764584350
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 8th, 2007, 02:17 AM
Registered User
 
Join Date: Feb 2005
Location: Seattle, WA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to raysot
Default Output Parameters: I can't be this stupid...

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
Reply With Quote
  #2 (permalink)  
Old March 9th, 2007, 02:30 AM
Registered User
 
Join Date: Feb 2005
Location: Seattle, WA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to raysot
Default

...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
Reply With Quote
  #3 (permalink)  
Old March 9th, 2007, 10:20 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 
Quote:
quote:It can't be this difficult to set an Output variable in SSIS


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




Reply With Quote
  #4 (permalink)  
Old March 11th, 2007, 06:53 PM
Registered User
 
Join Date: Feb 2005
Location: Seattle, WA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to raysot
Default


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
Reply With Quote
  #5 (permalink)  
Old April 1st, 2007, 12:00 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old April 10th, 2007, 12:29 AM
Registered User
 
Join Date: Feb 2005
Location: Seattle, WA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to raysot
Default

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
Reply With Quote
  #7 (permalink)  
Old March 31st, 2008, 07:27 PM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #8 (permalink)  
Old March 31st, 2008, 07:45 PM
Wrox Author
 
Join Date: Sep 2007
Location: Farmville, VA, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old April 1st, 2008, 01:47 PM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #10 (permalink)  
Old April 1st, 2008, 02:03 PM
Wrox Author
 
Join Date: Sep 2007
Location: Farmville, VA, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 6 "Using SQL Output Parameters..." burkecrosby BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 22 February 10th, 2010 05:33 PM
output parameters with the objectdatasource trancehead ASP.NET 2.0 Professional 2 August 25th, 2008 01:41 PM
Output Parameters Bob Bedell SQL Server 2000 2 March 25th, 2006 06:32 AM
OUTPUT Parameters saulodet ADO.NET 0 May 14th, 2004 11:02 AM
SQL output parameters ohiggs SQL Server 2000 4 February 9th, 2004 10:09 PM



All times are GMT -4. The time now is 08:17 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.