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
Web Languages and StandardsASP.NETMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Simone B

8/22/2022 - Mon
Simone B

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
Simone B

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Simone B

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Amour22015

ASKER
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
Amour22015

ASKER
Great
Simone B

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!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.