How to name SQL parameters in an SSIS Execute SQL Task

In this article we describe Step by step How to name SQL parameters in an SSIS Execute SQL Task , to avoid the common Sql Server Integration Services error : "Parameter name is unrecognized."  
We follow here the conventions introduced by  MSDN in the SQL Server documentation for SSIS . For explaining naming SQL parameters in an Execute SQL Task ,  we will make use of the following SSIS application:

How to name SQL parameters in an SSIS Execute SQL Task

We will want to give to the SQL parameters a proper name in order to avoid  the "Execute SQL Task" error, that can be seen in the picture above   : "Parameter name is unrecognized." :

The  MSDN Documentation explains that naming conventions for using SQL  parameters in OLE DB  should be as follows:

Suppose that we have this SQL query which is using parameters. Then we must use the "?" marker this way :

All parameters must be mapped to the query markers, and indexed base "0" . So, as we have 4 "?" markers, we must name 4 parameters indexed from "0" to "3" ,  using integer numbers conforming to the index positions:

      WHEN ?  - ? <  0          

      WHEN ?  - ? > 0   

        

     

    

That's all...Enjoy Dynamics CRM

by Carmel Schvartzman

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