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
WHEN ? - ? > 0
As you see, the parameters names are integer numbers conforming index positions.
We hope this article was useful to you.
by Carmel Schvartzman
כתב: כרמל שוורצמן