SQL Server Performance

Stored Procedure vs SSIS

Discussion in 'SQL Server 2008 Integration Services' started by darkangelBDF, Feb 19, 2010.

  1. darkangelBDF Member

    Hi there, all. I have written a stored procedure that merges table from one table into another table. As input parameters it accepts @SourceDatabaseName, @SourceTableSchema and @SourceTableName.
    My question is how would I be able to schedule this so that it runs on a regular basis?
    What it does is essentially as follows:
    We will enable CDC on several tables. The stored procedure accepts the input parameters and proceeds to merge the data in the CDC table into an audit table that we created. This will be done every 15 minutes for each table that is being monitored by CDC.
    I know the next question will sound stupid but I am very new to the whole development scenario.
    I have the stored procedure and if I run it exec uspStoredProc 'DBName', 'TableSchema', 'TableName', it runs and executes perfectly. The stupid question is "How will I schedule it so that it knows which tables to run it for without me having to enter the input parameters for each table?"
    I hope it makes sense. Any help would be appreciated.
  2. satya Moderator

    How frequently you need to run the job?
  3. darkangelBDF Member

    Hi there. They want it to run about every 15 minutes or so as it is a very busy OLTP system.
  4. gregj New Member

    Does the list of tables change often? How many tables? You could create a job (SQL Server Agent->Jobs in Management Studio) that executes that stored procedure for each table.
  5. darkangelBDF Member

    Nope, once everything is set up properly there won't be changes taking place often regarding the tables that are monitored. How would I set that job up so that I don't have to manually configure the SP to run per table?

Share This Page