Working with DTS Packages in SQL 2005

Once you have a repetitive task, that requires the same manipulation tasks to clean up your data, you will need to start using a combination of DTS packages and Stored Procedures.  A store procedure is nothing more than a query that is saved that you can call when needed. Stored Procedures are located in the Programmability portion of the SQL database.  Your procedure can be as simple or complex as you need it to be.  The Stored Procedure below is used to Delete data from a table

STORED PROCEDURE

USE [Dashboard]

GO

/****** Object:  StoredProcedure [dbo].[sp_Clear_Current_Dashboard]    Script Date: 11/06/2013 17:38:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE  PROCEDURE [dbo].[sp_Clear_Current_Dashboard]

AS

SET NOCOUNT ON

Delete from DATA_IMPORT_Q4_2012

Where not indicator_id in('13','21','34','35','61','200')

Once the Stored Procedure is created, you don’t need to save it. If at another time you need to update the procedure, right click on the Stored Procedure and select modify. It will open as ALTER PROCEDURE instead of CREATE.

CALL STORED PROCEDURE

To execute the Stored Procedure you simply write. That sp is for Stored Procedure.

execute [dbo].[sp_Clear_Current_Dashboard]