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/

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

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.

Thank you Mr. Kelley,

Your idea was awesome!

Frank in Indy,

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

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

JKennedy

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

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.

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

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.

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.