Techniques/FAQ - ssis execute sql task - Asked By srinivas on 07-May-12 11:14 PM

 Hi friends, small clarification in execute sql task.


First i execute stored procedure in sql server   create procedure sample (@empno int)
                                                                        as
                                                                       begin
                                                                      select * from sample where empno=@empno
                                                                        end
   and                 execute procedure     exec dbo.sample  7369
                                          That time it retrive what evere 7369 empno that record it retrive. its fine

so iam try to execute this same procedure in ssis by useing execute sql task.
firse i take one variable for empno      name      scope     datatype    value
                                                        empno    package    int             0

then i drag and drop execute sql task then  in execute sql task   in general tab   Result set=none and  conection type =oledb and  connection=localhost.sam.sa and sql source type =direct input and sql statement=exec dbo.sample ?
and in parameter mapping i do mapping ther empno like 
          variable name     direction     datatype   parametername  parametersize
            user::empno    input           long             newparameter     0
then after i clikck ok then i execute sql task. that time it doesnot given output its given errore.how to i acchive that task plz tell me 
Venkat K replied to srinivas on 08-May-12 01:04 AM
Can i know what is the error?
When you declare the empno variable have you specified any default value againt that?
srinivas replied to Venkat K on 08-May-12 10:31 AM
so iam try to execute this same procedure in ssis by useing execute sql task.
firse i take one variable for empno name scope datatype value
empno package int 0

then i drag and drop execute sql task then in execute sql task in general tab Result set=none and conection type =oledb and connection=localhost.sam.sa and sql source type =direct input and sql statement=exec dbo.sample ?
and in parameter mapping i do mapping ther empno like 
variable name direction datatype parametername parametersize
user::empno input long newparameter 0
then after i clikck ok then i execute sql task. that time it doesnot given output its given errore.how to i acchive that task plz tell me

errore ocured like
The query failed to parse. Syntax error, permission violation, or other nonspecific errore
 

srinivas replied to Venkat K on 09-May-12 02:23 AM
hi errore ocured when ever i execute sql task 
SSIS package "Package1.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec dbo.sample ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
SSIS package "Package1.dtsx" finished: Success.
The program '[2264] Package1.dtsx: DTS' has exited with code 0 (0x0).


how to solve .this task