Error in SSIS: Execute SQL Task Error

Today we will see how an error is raised in SSIS when the parameter mapping is not same in the Execute SQL Task.

For instance, we have a Store procedure (SP) created in a database with 2 parameters.

CREATE PROCEDURE USP_Store_Procedure
@TableName Varchar(20)
BEGIN
SELECT * INTO Testing FROM @TableName
END

This procedure creates a new table Testing with the same structure as the table name that is sent as parameter and all the data from the table that is sent as parameter to Store Procedure is inserted into Testing table in the database

Now, this procedure need to be executed in the SSIS. Open SSIS and drag and drop the Execute SQL Task.

In the parameter mapping page, select the variable name that need to be sent as parameter to the Store procedure in the database.

In Later stage, suppose if we(or somebody) adds another parameter to the database and if this parameter is not mapped in the SSIS package that was created earlier, then the package is failed with the below error message:

[Execute SQL Task] Error: Executing the query “EXEC USP_Store_Procedure ?…” 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.

Solution:

Check the parameters those we have passed in the SSIS package. Sometimes number of parameters that we have supplied and the number of parameters which are required doesn’t match, at that time we can get the above error.

• Check the number of ? that are passed in the Store procedure in Execute SQL Task matches with the number of parameters in the Store procedure in the database.
• Check the number of variables passed in the Parameter Mapping page in Execute SQL Task matches with the number of parameters in the Store procedure in the database.
• Check the datatype of the variables that are passed in the Parameter Mapping page matches with the datatypes of the parameters in the store procedure in the database.

Posted by SuneethaKB on September 21, 2011

https://suneethasdiary.wordpress.com/2011/09/21/error-in-ssis-execute-sql-task-error/