We help IT Professionals succeed at work.
Get Started

first SSIS package using output variables

233 Views
Last Modified: 2012-06-21
Hi

Experts I am migrating the below stored procedure to a SSIS package,  Iv not used SSIS before and not sure how I can replicate the passing of variable  as an output on a called sp and then use it later on, does anyone have any advice on how to do this or a quick guide
ALTER PROCEDURE [dbo].[spProcess_TRS]
AS
BEGIN

DECLARE @FromDate datetime    --Used to store the date where event_time is proecssed FROM
DECLARE @ToDate Datetime      --Used to store the date where event_time is proecssed TO
DECLARE @X int				  --Used to store the number of trades that have a report status of X
DECLARE @C int				  --Used to store the number of trades that have a report status of C
DECLARE @F int				  --Used to store the number of trades that have a report status of F
DECLARE @N int				  --Used to store the number of trades that have a report status of N

--Delete previous data
Delete from tblImport
Delete from tblTradeHistory
Delete from tblProcess


EXEC  dbo.spProcess_Import  @FromDate output, @ToDate output
PRINT N'IMPORT COMPLETE'

EXEC [dbo].[spProcess_Enrichment]
PRINT N'ENRICHMENT COMPLETE'

--Export the file 
EXEC dbo.spCreate_TRS_File
PRINT N'TRS FILE EXPORTED'

EXEC dbo.spProcess_HistorySanpShot
PRINT N'HISTORY SNAPSHOT COMPLETE'

SET @X  = (Select Count(Report_Status) from qryExport_All where Report_Status ='X')
SET @C  = (Select Count(Report_Status) from qryExport_All where Report_Status ='C')
SET @F  = (Select Count(Report_Status) from qryExport_All where Report_Status ='F')
SET @N  = (Select Count(Report_Status) from qryExport_All where Report_Status ='N')

Insert Into tbllog (FromDate,ToDate,RunDate,X,C,F,N) Values
(
@FromDate,
@ToDate,
Getdate(),
isnull(@X,''),
isnull(@C,''),
isnull(@F,''),
isnull(@N,'')
)

PRINT N'COMPLETION OF PROCESS'

END

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE