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).
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.
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 ..
...