Stored Procedures to SSIS 2005

If you leave this line in the task:

ALTER PROCEDURE [dbo].[stoDel999ExecuteAll]
AS

then every time you run the package, your procedure will alter. Of course, no changes will actually be made, because you haven't changed the script.

You will need to copy only the part of the procedure that does something.

Leave this in:

USE [DATA]
GO

Then start copying from here:

IF EXISTS (SELECT name ..
...

Hi and thanks,

so what you are saying is:

This is the stored Procedure:
USE [DATA]
GO
/****** Object:  StoredProcedure [dbo].[stoDel999ExecuteAll]    Script Date: 07/10/2012 12:34:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  Stored Procedure dbo.stoDel999ExecuteAll    Script Date: 4/26/2004 3:30:01 PM ******/
ALTER PROCEDURE [dbo].[stoDel999ExecuteAll]
AS
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzAccountSSNTemp'
        AND        type = 'U')
DROP TABLE zzzAccountSSNTemp
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzAccountSSN'
        AND        type = 'U')
DROP TABLE zzzAccountSSN
EXECUTE stoAccountSSNList001
EXECUTE stoAccountSSNList002
EXECUTE stoAccountSSNList003
EXECUTE stoAccountSSNList004
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzDelinquencyHistory'
        AND        type = 'U')
drop table zzzDelinquencyHistory
execute stoDel001DelinquencyHis
CREATE INDEX inxDelinqHistory ON zzzDelinquencyHistory (account)
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzCurrent'
        AND        type = 'U')
drop table zzzCurrent
execute stoDel002DelinquencyCurrent
CREATE INDEX inxCurrent ON zzzCurrent (acct_num)
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzDelinquents'
        AND        type = 'U')
drop table zzzDelinquents
execute stoDel003DelinquencyDelinq
CREATE INDEX inxDelinquents ON zzzDelinquents(acct_num)
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzDelinqTemp1'
        AND        type = 'U')
DROP table  zzzDelinqTemp1
execute stoDel004DelinqTemp1
CREATE INDEX inxDelinqTemp1 ON zzzDelinqTemp1(ssn)
IF EXISTS(SELECT name
        FROM        sysobjects
        WHERE  name = 'zzzDelinqTemp2'
        AND        type = 'U')
DROP table  zzzDelinqTemp2
execute stoDel005DelinqTemp2
CREATE INDEX inxDelinqTemp2 ON zzzDelinqTemp2(ssn)


And the only thing I take out (when copying to Execute SQL Task/SQLStatement) is:
ALTER PROCEDURE [dbo].[stoDel999ExecuteAll]
AS

You said:
What you could also do is to just execute the stored procedure in your SQL task:

exec DATA.dbo.stoDel999ExecuteAll

I think I am to do this just in SSIS, BIDS, Visual Studio

Thanks

That's correct. Once you take out the Alter statement, both options should give you the same result.

I recommend trying this first in a test environment.

I am running all in a test environment (Test Server)  I also thought about what you had said:
Using:
exec DATA.dbo.stoDel999ExecuteAll

I think that will work also.

Maybe you can help me with some other posts that involve SSIS 2005?
I have a post titled:
DAO to ADO.net
that I just opened

Thanks

Great

Thank you, I appreciate your confidence. But I'm afraid I'm 100% SQL. I don't know anything about DAO or ADO.

Good luck with your other posts!