Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)sql