xterminal


I'm brand-spankin'-new to SSIS, so I hope this is a simple, easy problem to fix, but I need everything in one-syllable words, thanks.

I have an Execute SQL task attempting to run a stored procedure:

exec risp_extract_pos_direct_data_competetive_test

(the SP has two parameters; I get the same results with two question marks. The connection is an OLE DB connection.)

I have two input variables mapped to the Execute SQL task, User::company_sysno (GUID) and User:: start_period_sysno (LONG). Depending on the iteration, I also have a returnvalue variable mapped, User::ErrorCode (I have tried every data type I can think of). The stored procedure does contain a RETURN @m_intErrorCode line, but the behavior I'm getting does not change if this line is commented out.

When the ResultSet is set to "none," I get the following error:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

When the ResultSet is set to either SingleRow or FullResultSet, I get the following, somewhat different, error:

There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

(Obviously, change the last bot of that to "FullResultSet" when FullResultSet is selected.)

I've found a handful of webpages dealing with each of those errors separately, but have not discovered one that deals with them in tandem, so I'm not even sure where to start looking. For that matter, I'm not even sure what else I need to tell you to give you an idea of what's going on. I'll post the stored procedure if necessary, but it's rather long, and my company are a tad paranoid about releasing code into the wild.

Thanks.





Re: Execute SQL Task gives varying errors.

Phil Brammer


What happens when you set the data types of the INPUT parameters (variables) to strings

Your other option is to build the execute stored procedure statement in a variable expression and use that variable as the SQL Source for the Execute SQL Task.






Re: Execute SQL Task gives varying errors.

Matt Masson - MSFT

You probably don't have to post the entire stored procedure, but I'd be interested to see what it regularly returns. You said there is RETURN statement that returns an int (error code), but what does it normally return.

I'm not sure if your problem is in the parameter mapping, as it appears the stored procedure is actually getting executed.

~Matt







Re: Execute SQL Task gives varying errors.

xterminal

>You said there is RETURN statement that returns an int (error code), but what does it normally return.<

Having just gone through the stored procedure specifically looking for instances of the variable name (I didn't write it), I have to say I'm kind of surprised if it ever returns anything but 0:

Line 39:

DECLARE @m_intError int,

Line 115:

SELECT @m_intError = 0

Line 258:

--RETURN @m_intError

Quicksearch finds no other references to the variable.

Gets better, though. I switched to an ADO.NET data provider and now have an entirely different error:

Error: 0xC002F210 at Run stored procedure, Execute SQL Task: Executing the query "exec risp_extract_pos_direct_data_competetive_test @company_sysno" failed with the following error: "No mapping exists from DbType 72 to a known SqlDbType."

I can't find a sinle webpage, BOL entry, or anything else that references "DbType 72." For what it's worth, the input variable (User::company_sysno) is GUID, and the parameter (@i_GuidCompanySysno) is UNIQUEIDENTIFIER.






Re: Execute SQL Task gives varying errors.

Phil Brammer

So, again. What happens when you change the INPUT parameter types to VARCHAR

That is, on the parameter mapping tab, set the data types to VARCHAR.





Re: Execute SQL Task gives varying errors.

xterminal

Answered my own question, though I have no idea whether it's an "answer" or just a workaround.

Running the stored procedure in a OLEDB source in the data flow step instead of running it in a separate ExecuteSQL step caused all the errors to go away, as if by magic. Why, I've no idea, but it works.