Thursday, April 16, 2015

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


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


     How to use   Execute SQL Task



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

How to use SQL parameters in an Execute SQL Task 1





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

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


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

 How to use SQL parameters   2

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


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