SSIS: Value does not fall within the expected range

SSIS Error

[Execute SQL Task] Error: Executing the query "EXEC dbo.sp_name ?,?,? OUTPUT" failed with the following error:

 "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, 

"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When you are trying to execute a stored proc though execute SQL task:

you can call sp like 

            EXEC dbo.sp_name ?,?

when there is no out put parameter

and like 

           EXEC dbo.sp_name ?,?,? OUTPUT

where there is an output parameter.

If you try to assign parameter names like above in this case you will receive error that 

"Value does not fall within the expected range."

and it gives no clue whats so ever regarding the issue with the task

this can be fixed by replacing actual name of parameters in Parameter Name Column for Index location of the parameter that you have defined in your stored proc 

and  parameter mapping tab is going to look like and  Bingo your error is gone....

could not UPDATE table "[MSDASQL]"

--update any record

UPDATE ic SET

                        Display_Name__c =ISNULL(U.Display_Name__c ,'')

                        ,Display_order__C=ISNULL(U.Display_order__C ,0)

                        ,is_Active__c=U.IsActive__C

                        ,Name=ISNULL(U.Name ,'')

                        ,Parent__C=ISNULL(U.Parent__C,'')  

                        ,cost__C=ISNULL(U.cost__C,0)

                        

--DELETE FROM ic

--Select *

FROM

OPENQUERY(LINKEDSERVER,'Select

                        Id

                        ,Display_Name__c

                        ,Display_order__C

                        ,is_Active__C

                        ,Name

                        ,Parent__C 

                        ,cost__C

                        

             from

                  item

                  ') ic

INNER JOIN #UPDATES U

 ON ic.id=U.Id

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "SetPos update return error".

Msg 7343, Level 16, State 4, Line 296

The OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" could not UPDATE table "[MSDASQL]".

And I get following error with as is_Active__C field in PostGres is boolean and OpenQuery unable to update , once remove this column and query works fine.

other alternative is delete this record and insert the whole record again.

DELETE Query:

DELETE FROM ic
--Select *FROMOPENQUERY(LINKEDSERVER,'Select
                        Id                        ,Display_Name__c                        ,Display_order__C                        ,is_Active__C                        ,Name                        ,Parent__C                         ,cost__C                                     from                  item                  ') ic
INNER JOIN #UPDATES U
 ON ic.id=U.Id

INSERT Query:

            INSERT INTO OPENQUERY(BUYBACK_STG,'Select

                        Id

                        ,Display_Name__c

                        ,Display_order__C

                        ,is_Active__c

                        ,Name

                        ,Parent__C 

                        ,cost__C

             from

                  item

                  ')

SELECT

                        Id

                        ,ISNULL(Display_Name__c ,'')

                        ,ISNULL(Display_order__C ,0)

                        ,IsActive__C

                        ,ISNULL(Name,'')

                        ,ISNULL(Parent__C,'')  

                        ,ISNULL(cost__C,0)

                        FROM #UPDATES