May 7, 2012 at 9:15 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
May 7, 2012 at 10:04 pm
You have to insert that the Results into a Table,because the Execute Sql Task doesn't display resultant data while executing the ssis package..
If you want to work with the results displayed by your select stmt, change the ResultSet property to FULLRESULTSET...
Can you please explain more on what you will do with the data displayed by proc.
May 8, 2012 at 12:20 am
Also post the error message. It's kind of hard to troubleshoot without it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 8, 2012 at 6:49 am
Hi,
In execute sql task, parameter mapping
variable nameDirectionData typeparameter name parameter size
user::empnoInput Long 0 -1
If u consider like this in execute sql task then it wont show the error .
🙂
May 8, 2012 at 8:17 am
errore ocured like
The query failed to parse. Syntax error, permission violation, or other nonspecific errore
May 8, 2012 at 8:20 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
May 8, 2012 at 11:08 pm
Hi,
The error which u mentioned will be execute when u parse the query but if u try to run the execute task it will run successfully .
May 9, 2012 at 12:11 am
SSIS package "Package40.dtsx" starting.
Warning: 0x80000036 at Package40: There are 3 days left in the evaluation. When it expires, packages will not be able to be executed.
Warning: 0x80000036 at Package40: There are 3 days left in the evaluation. When it expires, packages will not be able to be executed.
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
Warning: 0x80019002 at Package40: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package40.dtsx" finished: Failure.
why its occured
May 9, 2012 at 12:22 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
May 9, 2012 at 12:35 am
The error which u mentioned above , in that parameter name is showing as unrecognized so may be the parameter which has chosen is not in that scope of package so take a new package and create a new variable on package level and pass that variable in execute sql task and try the same procedure as before mentioned.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply