none
stored procedure error while executing from SSIS execute sql task.

    Question

  • Hi all,

    I have a ssis package , in that one of execute sql task calling the stored procedure.

    If i tried to execute the package i am getting below error msg.

    "[Execute SQL Task] Error: Executing the query "Declare @cur_date datetime set @cur_date = getdate()  Exec sp_MD_JobTrackingInsertUpdate 0, ?, ?, @cur_date, Null, 'Ongoing..', ? output " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

    thanks in advance

    Regards,

    Ravikumar.

    Wednesday, July 04, 2012 9:09 AM

Answers

  • what is data type of parameters in stored procedure? and what is data type of variable in SSIS?

    did you tried this: DateTime type in SSIS? and datetime data type in stored procedure? and DBTimeStamp in variable mapping tab?


    http://www.rad.pasfu.com

    • Marked as answer by Eileen Zhao Tuesday, July 10, 2012 8:31 AM
    Wednesday, July 04, 2012 10:40 AM
    Moderator

All replies

  • could you paste whole script here?

    another question; did you mapped parameters correctly? what is parameter names? and what type of connection you used?

    if you use OLE DB connection; parameter markers should be question mark and parameter names should be index starting from 0,1, 2....


    http://www.rad.pasfu.com

    Wednesday, July 04, 2012 9:15 AM
    Moderator
  • Hi Reza Raad,

    finally i found mistake ,but can't able to resolve it.

    In my Stored procedure Start_date and End_date two parameters are there.if i pass values through parameters ssis will not allow because in expression level ssis and sql server date functions are different.

    but how do to, i don't no.can any suggestion on this?

    Wednesday, July 04, 2012 10:31 AM
  • what is data type of parameters in stored procedure? and what is data type of variable in SSIS?

    did you tried this: DateTime type in SSIS? and datetime data type in stored procedure? and DBTimeStamp in variable mapping tab?


    http://www.rad.pasfu.com

    • Marked as answer by Eileen Zhao Tuesday, July 10, 2012 8:31 AM
    Wednesday, July 04, 2012 10:40 AM
    Moderator