SSIS Generic Error for SP Execution

0
votes

In a for each loop container , for each job reference.

I am running an execute sql task to get a list of 21 INPUT parameters from my stored procedure ( Task 1 ). The INPUT parameters are then stored in SSIS variables.

Then I have an execute sql task to run the stored procedure, where I set the 21 Input Paramters and an Output parameter. ( Task 2 )

When running the package it fails at Task 2 with the following generic error:

Error: 0xC002F210 at Run sp_insert_Package, Execute SQL Task: Executing the query "DECLARE @TestFaultStageID int DECLARE @TestID..." 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. Task failed: Run sp_insert_Fault2 Error: 0xC002F210 at Run sp_insert_Package, Execute SQL Task: Executing the query "DECLARE @TestFaultStageID int DECLARE @StoreID..." 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. Task failed: Run sp_insert_Fault2 Warning: 0x80019002 at 02-01-02-Epoch-Jobs-Load: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

I have tried to find the error, by checking all the data types and conversions and other stuff but no luck so far. Is there an easy way to track down or narrow down cause ?

Sql Server 2012

2
sqlsql-serverssis
try to execute the sp manually with the parameter. - Mattia Caputo
Works perfectly in database side. Problem something to do with SSIS Setup. - James Khan
you have to check with breakpoint if the variable are setted correctly - Mattia Caputo
Checked all variables in a watch window . Only diff was in watch window variable diaplayed as User::LastEdiDate {6/28/2016 1:57:21 PM} DateTime compared to SET @LastEdiDate='2016-06-28 13:57:21.590' in Databasase SP statement. - James Khan
Can you run sql profiler to watch for the point of failure - its my give more insight to the underlying cause of the problem. And as soon as I see dates related to errors, I automatically think YMD vs YDM issues, but that might just be a red herring. - Phil Blackburn

2 Answers

1
votes

In Execute SQL Task component, in SQLStatement field, you should have question marks (?) set in place where you want to put parameters. They should also be defined in Parameter mapping tab.

Are you sure those are set correctly? Could you share your SQLStatement field value?

0
votes

Sorted ? missing for the OUTPUT parameter was the problem. Sorry All ! Arghhhh