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
  #21 (permalink)  
Old August 16th, 2007, 10:38 AM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)

Reply With Quote
  #22 (permalink)  
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

Quote:
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
http://www.sqlservercentral.com/columnists/bkelley/
http://www.truthsolutions.com/
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

http://blog.boxedbits.com/archives/8

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.)

Regards


Paul Simon
Reply With Quote
  #23 (permalink)  
Old February 10th, 2010, 04:33 PM
Registered User
 
Join Date: Feb 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default This is a good resource

http://blogs.msdn.com/mattm/archive/...arameters.aspx

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 04:39 PM.
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
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 05: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 09:09 PM



All times are GMT -4. The time now is 09:37 AM.


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