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
  #11 (permalink)  
Old March 17th, 2006, 09:27 PM
Registered User
 
Join Date: Mar 2006
Location: Columbia, SC, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #12 (permalink)  
Old April 4th, 2006, 07:17 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mapping the column [FileName] to user::MyFile raised some error saying that mapping by column name in a result set was not supported by this type of connection. Using the column number (0) did the trick... So on the result set tab I mapped the column "Result Name" with value 0 to "Variable Name" user::MyValue

Reply With Quote
  #13 (permalink)  
Old May 17th, 2006, 11:06 AM
Registered User
 
Join Date: May 2006
Location: Indianapolis, IN, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What is taking so long for the publisher to provide a fix for this? We all have been waiting a long time now! Since I live in Indianapolis, I'm going to have to drive up to their office and knock on their front door and personally ask for the fix.
Reply With Quote
  #14 (permalink)  
Old May 17th, 2006, 12:58 PM
Registered User
 
Join Date: May 2006
Location: Indianapolis, IN, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Mr. Kelley,

Your idea was awesome!

Frank in Indy,

P.S. You save me a trip to their publishing facility!! ;)

Reply With Quote
  #15 (permalink)  
Old May 25th, 2006, 10:08 AM
Registered User
 
Join Date: May 2006
Location: Brightwaters, NY, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Any Help here from the publisher would be appreciated has this been resolved

JKennedy
Reply With Quote
  #16 (permalink)  
Old June 8th, 2006, 07:34 AM
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is another solution...

In the Execute SQL Task Editor do the following:
1) General -> Change the ConnectionType to OLEDB
2) General -> Change the SQLStatement to EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARM', 'STRING'
3) General -> Change the ResultSet to Single row
4) Parameter Mapping -> Remove all parameter mapping
5) Result Set -> Add a Result Name of MYFILENAME with a Variable Name of User::MyFile

Finally, change the sproc to the following:
ALTER PROC dbo.usp_GetConfigParamValue(
    @ApplicationName VARCHAR(30),
    @ParameterName VARCHAR(50),
    @ParameterTypeName VARCHAR(30)
)
AS
SET NOCOUNT ON
SELECT 'C:\Execute SQL Sample Changed.txt' AS MYFILENAME
SET NOCOUNT OFF


HTH,
Damon

Reply With Quote
  #17 (permalink)  
Old July 7th, 2006, 04:42 AM
Registered User
 
Join Date: Jul 2006
Location: Nottingham, , United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have tried all of the above "fixes" and still can't get the SQL Task to execute...##

Chris Boardman
Software Developer
Tribal Group PLC.
Reply With Quote
  #18 (permalink)  
Old July 16th, 2006, 09:45 PM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you bkelley and dlherren.
Finally got this to work after changing to OLE DB and following your steps.


Would be interest in how to work with ADO.NET and if there would be any benefits of using this connection type over OLE DB.

pdb

Reply With Quote
  #19 (permalink)  
Old October 3rd, 2006, 05:41 AM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

another solution using the datareader

- execute sql task :
calling the stored procedure to get the config-param : exec stp_GetConfigParamValue 'MYAPP','MyParam','String',? output
and use 0 as parameter name in the parameter mapping
(For all connections except ADO.NET, you must use a ?, see BOL)

- data flow from datareader to Flat file destination.
I received errors on the 'flat file connection manager' concerning the connection string witch contained illegal characters.
I solved this by changing the expression to (DT_WSTR, 200)@[User::MyFile]

Hopefully this post saves some time in troubleshooting this exercise.

Reply With Quote
  #20 (permalink)  
Old December 15th, 2006, 10:43 AM
Authorized User
 
Join Date: Dec 2006
Location: Lake Wylie, SC, USA.
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SP2 v9.0.3027 resolves the invalid datatype issue when returning a string from a stored procedure into a string parameter using ADO.NET. You now have the ability to assign a 'Parameter Size' in the Parameter mapping dialog.

For the SP2 CTP, visit http://www.microsoft.com/sql/ctp.mspx.
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 10:49 AM.


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