Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

Stored Procedures to SSIS 2005

Hi and Thanks,


I have been Tasked with copying a Stored Procedure from MS SQL Server 2005 into: BIDS, Visual Studio 2005, SSIS 2005

I guess all you do is Create a SSIS Package(name it the same as the stored procedure you are going to copy, Click and drag Execute SQL Task, go to SSMS find the stored procedure, right click and select modify, select all, copy the SQL Statement, then go back to Visual Studios 2005,  Double click on Execute SQL Task,  Click on SQLStatement, and paste the Stored Procedure(SQL statement).

Please see Stored1:


Let me know if this is correct?


Thank You
Stored1.docx
Avatar of Simone B
Simone B
Flag of Canada image

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 ..
...
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Amour22015
Amour22015

ASKER

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!