Wrox Programmer Forums
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 16th, 2007, 10:38 AM
Registered User
Join Date: Aug 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Took a day and a half, but I got the exec spusp_GetConfigParamValue 'MYAPP','MYPARM', 'STRING', @MYVALUE sql task to work:
1) kept isstoredproc as false
2) on parameter mapping, said output datatype was object
3) in the proc definition, said output was a sql_variant, not a varchar(255)

Old October 20th, 2009, 10:23 AM
Registered User
Join Date: Oct 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Additional Step and Alternative to SQL Output Param

Originally Posted by bkelley View Post
I received the same error, both when I created the package from scratch and also when I used the sample which is downloadable from the site. I was able to get the example to work, but only after forcing the filename out as a resultset. See page 150 where it talks about possible issues and this is given as a possible workaround.

What I did:
  • Removed the @ParameterValueVar parameter
  • Added a SELECT statement to the stored procedure to return the value as a column named [FileName]
  • Removed the @MYVALUE OUTPUT from the SQLStatement
  • Changed ResultSet to a single row
  • Deleted the parameter mapping
  • Created a mapping under Result Set to map FileName to User::MyFile

K. Brian Kelley, MCSE, GSEC(Gold)
Regular Columnist, SQLServerCentral.com and SQL Server Standard Magazine
I found that I also had to change the type from ADO.NET to OLEDB on the EXECUTE SQL Task

However, I then found this other link


Which allows you to use a script to populate all variables from the parameters table. (Only works for Strings. However, Strings is not much of a limitation as most of what we usually want to change are file paths, server names, etc. The other limitation is that the vars must already be created. I am looking to see if there is some code that will create the vars, ideally at global scope.)


Paul Simon
Old February 10th, 2010, 05:33 PM
Registered User
Join Date: Feb 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default This is a good resource


This page explains the different settings for different connection types very well.

I looked back in the book and noticed that it was clearly stated in step 4 on page 149 to use "ADO.NET", that was my problem! The default is OLEDB and so I didn't pay attention.

I wonder if many of you did not miss the same thing because many of your solutions involving changing to OLEDB but you don't have to.

The original example in the book works fine.

Under General:
ConnectionType: ADO.NET
SQLStatement: EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARM', 'STRING', @MYVALUE OUTPUT

Under Parameter Mapping:
VariableName: User::myFile
Direction: Output
Data Type: String
Parameter Name: MyValue
Parameter Size: -1

And that is it.

Last edited by rayd; February 10th, 2010 at 05:39 PM..

Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Parameters: I can't be this stupid... raysot BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 13 May 20th, 2010 07:28 PM
output parameters with the objectdatasource trancehead ASP.NET 2.0 Professional 2 August 25th, 2008 12: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 10:02 AM
SQL output parameters ohiggs SQL Server 2000 4 February 9th, 2004 10:09 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.