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."

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

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:

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." :

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 :

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

We hope this example will be useful to you.

Happy programming.....

      by Carmel Schvartzman

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