Stored Procedure vs SSIS | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Stored Procedure vs SSISHi 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.
How frequently you need to run the job?
Hi there. They want it to run about every 15 minutes or so as it is a very busy OLTP system.
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.
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?