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
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 .
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
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.
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(
SET NOCOUNT ON
SELECT 'C:\Execute SQL Sample Changed.txt' AS MYFILENAME
SET NOCOUNT OFF
- 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.