Monday, April 13, 2015

How to fix the SSIS error : "Parameter name is unrecognized."

In this post we describe Step by step How to fix the SSIS error : "Parameter name is unrecognized."   in Sql Server Integration Services .
We implement here in just 5 minutes the conventions introduced by Microsoft in the MSDN SQL Server 2014 documentation for SSIS : (applies also to SQL 2005-2008) For sampling this fixing we use the following SSIS application:

 How to fix the SSIS error :  Parameter name is unrecognized.


How to fix the SSIS error : "Parameter name is unrecognized."


In the snapshot above it can be seen that there is a runtime error at an "Execute SQL Task" :
 SSIS error :  Parameter name is unrecognized.




As it is sensible to do, we have defined an SQL parameter and named it "ComputerName". Why? Because the default when adding a new parameter is as follows:

Parameter name is unrecognized.

The default parameter name is "NewParameterName". Therefore, in our example , we have customized it to "ComputerName". And, consequently, we use it in our SQL query as "@ComputerName".
However , we receive the following error : "Parameter name is unrecognized." :


  fix  SSIS error :  Parameter name is unrecognized.



According to MSDN Documentation, while using SQL the parameters must be named as follows:






Therefore we must change our SQL query to complain to those directives, and use the "?" marker :

 How to fix the SSIS error :  Parameter name is unrecognized. 1


Now, the parameters must be mapped to the query, indexed on base "0" .
In our example, we have 4 "?" markers: therefore we add 4 parameters indexed from "0" to "3" :

SELECT  
CAST(
CASE
      WHEN ?  - ? <= 0  
         THEN 0
      WHEN ?  - ? > 0   
         THEN   1
     
      ELSE -1
      END
      
AS FLOAT
)

AS  RESULT

 How to fix the SSIS error :  Parameter name is unrecognized.   2


We hope this example will be useful to you.

Happy programming.....

      by Carmel Schvartzman


כתב: כרמל שוורצמן