Post reply

SSIS step to call sp proc

  • December 31, 2018 at 1:55 pm

    #381106

    I have a table which has the details of all sp spoc names and executables, so I will have a step to call exec or sp proc. 
    So, say I am using the tsql step then what will be my code look like?
    sample
    Select category from log table
    where filetype = sql or exe ??
    and then run

  • January 1, 2019 at 11:33 am

    #2017700

    Not really sure what you're after here. What are you trying to actually achieve? What is your Execute T-SQL task meant to do, execute all/a procedure listed in the table?

    What do you mean by "executable"? You use the EXEC command to execute a Stored Procedure, but "executable" as an object is normally use to refer to files with the extension ".exe"; which aren't executable by SQL Server.

    Why do you have your own table that stores all the procedures/objects in your database as well? There are already objects in SQL Server that return that information in the sys schema. For example, sys.procedures has data on every object is the database that is classed as a procedure.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • January 4, 2019 at 1:01 am

    #2017952

    There are two ways to this, you can get the list of stored procedures into a variable (object type) then process it in two ways :
    1. Process the stored procedures using C# in a script task, you will need to pass the object parameter into the script task.
    2. Having the store procedures in the parameter object, you can have a loop task to consume it, load each procedure into a variable and pass it to the execute task component for execution.

    I believe this can be of help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply