Link to home
Create AccountLog in
Avatar of MrDavidThorn
MrDavidThorn

asked on

first SSIS package using output variables

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

ASKER CERTIFIED SOLUTION
Avatar of vdr1620
vdr1620
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of MrDavidThorn
MrDavidThorn

ASKER

Thanks dude, couldnt find much info on the way about this
NP..