Execute SQL Task Stored Procedure Variable Problem

[buddafish]

Hello All -

I am running into a brick wall with a simple SQL Task attempting to return (output) the value from a stored procedure into a global variable. I have tried both OLE DB and ADO.NET connection types and have Googled for hours (finding a lot of complaints) but no solution thus far.

Connection is good, variable declared at project level (Int32), result set variable is named the same as column name in the sproc, and all is 'green' but the variable does not get changed (remains at -1)

SQL Statement: exec DAILY_INTPRD_ROWCOUNT_sp ? output
Result Set: Single Row

Anybody have anymore input or ideas? I would like to get this to work because of the heavy use of SSIS for my ETL process - else I would just code this type of stuff and be done.

Thanks in advance -
Scott

 

[mrdenny]

The the connection type to OLE DB, and set the Parameter name to "0". When using OLE DB you have to use the index number of the variable.

In my package the command looks like this.

SET ARITHABORT ON
exec  dbo.asp_ProcessURLFile @FileId = ? OUTPUT

And my variable name is 0.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog