How to use SQL parameters in an Execute SQL Task in SSIS

In this post we describe Step by step How to use SQL parameters in an Execute SQL Task  in SSIS , to avoid the error : "Parameter name is unrecognized." ,  in Sql Server Integration Services.
We comply here with the recommendations introduced by Microsoft in the MSDN SQL Server 2014 documentation for SSIS : (applies also to SQL 2005) . For sampling using parameter names in an Execute SQL Task ,  we use the following SSIS application:

How to use SQL parameters in an Execute SQL Task  in SSIS

We want to adequately name SQL parameters in order to avoid  a runtime error at an "Execute SQL Task", as seen in the snapshot above   : "Parameter name is unrecognized." :

At the  MSDN Documentation, can be learned that using SQL  parameters in OLE DB must be done as follows:

Therefore if we have an SQL query using parameters, we must use the "?" marker as follows :

Those parameters will be mapped to the query, indexed on base "0" then, since we have 4 "?" markers, we should add 4 parameters indexed from "0" to "3" :

      WHEN ?  - ? <  0  

         THEN ....

      WHEN ?  - ? > 0   

         THEN   ....

     

    

As you see, the parameters names are integer numbers conforming index positions.

We hope this article was useful to you.

Happy programming.....

      by Carmel Schvartzman

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