April 13, 2011 at 9:15 am
Hi I have a SSIS package which hangs internally at the DaTAFlow task .I am using oledb Source to execute the sp with a date parameter.
Using the command below in the text area
EXEC usp_MyProc @Date = ?
and in the Set Query parameter window
Parameters Variables
@Date User:Date
ALTER PROCEDURE [dbo].[usp_MyProc]
@Date DATETIME
AS
SET NOCOUNT ON
SET FMTONLY OFF
IF 1 = 0
BEGIN
SELECT
CAST(NULL AS INT) AS ProcessorID,
CAST(NULL AS VARCHAR(50)) AS Terminal,
CAST(NULL AS VARCHAR(19)) AS PAN,
CAST(NULL AS MONEY) AS RequestedAmt,
CAST(NULL AS VARCHAR(50)) AS TxnSequenceNumber,
CAST(NULL AS VARCHAR(6)) AS AuthCode ,
CAST(NULL AS INT) AS NetworkID,
CAST(NULL AS VARCHAR(6)) AS TxnCode,
CAST(NULL AS MONEY) AS Surcharge,
CAST(NULL AS DATETIME) AS TxnSettlementDate,
CAST(NULL AS DATETIME) AS TxnDateTime,
CAST(NULL AS VARCHAR(200)) AS ATMTW_File_Name
END
-- Do real work starting here
CREATE TABLE #EFUNDSFI
(
ProcessorID INT NULL,
Terminal VARCHAR(50)NULL,
PAN VARCHAR(19)NULL,
RequestedAmt MONEY,
TxnSequenceNumber VARCHAR(50)NULL,
AuthCode VARCHAR(6)NULL,
NetworkID INT NULL,
TxnCode VARCHAR(6)NULL,
Surcharge MONEY ,
TxnSettlementDate DATETIME NULL,
TxnDateTime DATETIME NULL,
ATMTW_File_Name VARCHAR(200) NULL
)
INSERT INTO #TEST
SELECT DISTINCT
txn.ProcessorID,
txn.Terminal,
txn.PAN,
txn.Amount As RequestedAmt,
txn.TerminalSequenceNumber AS TxnSequenceNumber,
'0000' AS AuthCode ,
txn.NetworkID,
TxnCode = CASE WHEN txn.TxnTypeID IN( 1,50) THEN '010100'
WHEN txn.TxnTypeID = 4 THEN '040100'
WHEN txn.TxnTypeID = 5 THEN '050100'
END,
txn.Surcharge,
txn.SettlementDate AS TxnSettlementDate,
txn.ActivityTime AS TxnDateTime,
CONVERT(VARCHAR(8),txn.SettlementDate,110) +'/' +min(Processor.Name) AS ATMTW_File_Name
FROM [dbo].[A] txn
INNER JOIN [dbo]. Processor on processor.code = txn.ProcessorID
WHERE txn.SettlementDate = @Date
AND txn.TxnTypeID IN (1,4,5,50)
AND txn.ProcessorID = 3
GROUP BY
txn.ProcessorID,
txn.Terminal,
txn.PAN,
txn.Amount,
txn.TerminalSequenceNumber,
txn.NetworkID ,
txn.TxnTypeID,
txn.Surcharge,
txn.SettlementDate,
txn.ActivityTime
SELECT * FROM #TEST
DROP TABLE #TEST
RETURN
Please help.
Thanks,
April 14, 2011 at 12:12 am
Why are you creating the temp table #EFUNDSFI, as you don't use it afterwards?
Why are you using the temp table #test? You are inserting all those records into a temp table, just to get them out again with a SELECT *?
Get rid of the temp tables and just do the select with the inner join directly. See if that helps.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply