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.
About these ads
Leave a comment

1 Comment

  1. Priya

     /  April 10, 2013

    Superb!!! well explained and it really helped me to solve the problems i was facing in my package !! Great Work :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: