locked
Executing multiple stored procedure in Execute SQL task RRS feed

  • Question

  • Hi.

    I have 8 stored procedures which need to be run one after another. I added a Execute SQL task in SSIS and gave the below information: 

    SQLSourceType: Direct Input

    IsQueryStoredProcedure : True

    SQLStatement box -  I gave as below:

    sp_FirstSPName
    GO
    sp_SecondSPName
    GO

    I am getting the error when execting the task . Is this the right way to execute multiple SP ?

    Thanks

    Wednesday, April 29, 2020 8:52 AM

All replies

  • GO is not valid SQL, it's a command for tools like SqlCmd, remove them. And if you have more the one SP then you have to use the keyword EXEC/EXECUTE

    EXEC sp_FirstSPName;
    EXEC sp_SecondSPName;


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 29, 2020 9:04 AM
  • Thanks for the reply.

    I removed the GO statement and added EXEC at the start and a ; at end of SP.

    When I execute, I am getting error as -

    [Execute SQL Task] Error: Executing the query "EXEC sp_spFirst;

    EXEC sp_Secon..." failed with the following error: "Could not find stored procedure ''.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Wednesday, April 29, 2020 9:09 AM
  • "Could not find stored procedure ''.". ... or connection not established correctly.

    Does the SP's exists in the database where the package connects to? Does the current user/service account do have permissions to execute the SP's?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 29, 2020 9:52 AM
  • Hi .

    SP are existing in the DB. I have re-checked the database with SP_helptext and all SP are available. The user has the access to execute the SP.

    Thanks

    Wednesday, April 29, 2020 9:58 AM
  • Hi ,

    May I know if you connect to the proper database?

    May I know if these stored procedures are stored in the same database?

    Please try to add "Use DatabaseName" before exec ProcedureName.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 29, 2020 10:34 AM
  • Are the SP's in the same schema as the users default schema? Otherwise you have to add the schema name like 

    EXEC dbo.sp_FirstSPName;
    EXEC dbo.sp_SecondSPName;

    P.S.: Are the SP's really named as "sp_.."? Not a good idea, the prefix "sp_" is for system SP's and SQL Server always first look them up in system database "master".

    See CREATE PROCEDURE: "Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name."


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 29, 2020 10:53 AM