Loading and Updating Temp tables in SSIS

[burakcan]

Hi All,

I have a SSIS package reads 5 tables from another server. I am using Temp Tables to load the 5 tables then update the first temp table with other 4 temp tables before I load the final table .
It loads the temp tables but doesn't work when I try to update the first temp table with others. Package runs with out any error.
Please see the creation of the tables script and update script below :

Any help greatly appreciated.
Thanks


IF exists (
SELECT * FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')

AND o.id = OBJECT_ID(N'tempdb..##PI_CC_CreditLimitChngAccounts')
)
DROP TABLE ##PI_CC_CreditLimitChngAccounts;

CREATE TABLE ##PI_CC_CreditLimitChngAccounts(
[RN] [int] NULL,
[Date_Key] [int] NOT NULL,
[TSYS_Acct_Id] [bigint] NOT NULL,
[Event_CDate] [Date] NULL,
[SalesBR_To] [varchar](8) NULL,
[SALESID_TO] [varchar](10) NULL,
[CL_From] [decimal](18, 2) NULL,
[CL_TO] [decimal](18, 2) NULL,
[CPC] [varchar](3) NULL,
[CCNumber] [varchar](16) NULL,
[CC_CL_INCREASE] [varchar](13) NULL,
[SR_Eligible] [char](1) NULL,
[PreApproved] [char](1) NULL,
[EDW_P_CUST_ID] [bigint] NULL,
[EDW_B_CUST_ID] [bigint] NULL,
[Employee_Key] [int] NULL,
[Channel_Key] [int] NULL,
[Product_Key] [int] NULL
)

IF exists (
SELECT * FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')

AND o.id = OBJECT_ID(N'tempdb..##CC_SR_DAILY')
)
DROP TABLE ##CC_SR_DAILY;

CREATE TABLE ##CC_SR_DAILY(
[TSYS_Acct_ID] [bigint] NULL,
[Sales_Date] [datetime] NULL,
[Sales_Branch] [smallint] NULL,
[Sales_Login_ID] [varchar](8) NULL,
[SR_Product_Code] [varchar](4) NULL
)

IF exists (
SELECT * FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')

AND o.id = OBJECT_ID(N'tempdb..##CLK_CREDIT_APP_DAILY')
)
DROP TABLE ##CLK_CREDIT_APP_DAILY;

CREATE TABLE ##CLK_CREDIT_APP_DAILY(
[Tsys_Acct_Id] [bigint] NULL,
[Create_Dt] [datetime] NULL,
[New_Inc_Ind] [varchar](1) NULL,
[Sales_ID] [varchar](10) NULL,
[Sales_Transit] [int] NULL
)


IF exists (
SELECT * FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')

AND o.id = OBJECT_ID(N'tempdb..##CLIP_Monthly')
)
DROP TABLE ##CLIP_Monthly;


CREATE TABLE ##CLIP_Monthly(
[Date_Key] [int] NOT NULL,
[OfferDate] Date NOT NULL,
[Tsys_Acct_ID] [varchar](14) NULL,
[Offer_Type_Cd] [varchar](1) NULL,
[Total_Credit_Limit] [real] NULL
)


IF exists (
SELECT * FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')

AND o.id = OBJECT_ID(N'tempdb..##FICO_CLIP_Response')
)
DROP TABLE ##FICO_CLIP_Response;

CREATE TABLE ##FICO_CLIP_Response (
[Tsys_Acct_ID] [bigint] NOT NULL,
[Date_Key] [int] NOT NULL,
[OfferDate] Date NOT NULL,
[Test_Control] [varchar](10) NULL,
[#CLI_Mth0] [int] NULL,
[#CLI_Mth1] [int] NULL,
[#CLI_Mth2] [int] NULL
)



--******************Update statement******************

-- Remove duplicates due to Credit Limit increases due to a combination of pre-approved and non-preapproved increase

DELETE FROM ##PI_CC_CreditLimitChngAccounts WHERE RN > 1

-- Attach SR Sales Branch and Employee from the Daily SR Table since Aug 2016
UPDATE CL
SET SALESBR_TO = SR.Sales_Branch,
SALESID_TO = SR.Sales_Login_ID,
SR_Eligible = 'Y'
FROM ##PI_CC_CreditLimitChngAccounts CL
INNER JOIN ##CC_SR_DAILY SR
ON CL.TSYS_Acct_ID = SR.TSYS_Acct_ID
AND CONVERT(DATE,CL.[Event_CDate],101) = CONVERT(DATE,SR.Sales_Date,101)
AND SR.SR_Product_Code IN ('V997','V997','V730','V340','V330','V350','V999','V310','V997',
'V975','V226','V224','V224','V210','V220','V227','V320','V223','V227','V410','V997','V520')


-- Update Sales Branch and Employee from application system if not in SR system
UPDATE CLI
SET SALESBR_TO = CLK.Sales_Transit,
SALESID_TO = CLK.Sales_ID
FROM ##PI_CC_CreditLimitChngAccounts CLI
INNER JOIN ##CLK_CREDIT_APP_DAILY CLK
ON CLI.TSYS_Acct_ID = CLK.TSYS_Acct_ID
AND CLK.New_INC_IND = 'I'
AND CLI.[Event_CDate] = CLK.Create_Dt
WHERE SaleSID_TO IS NULL



--Update [PreApproved]
UPDATE ##PI_CC_CreditLimitChngAccounts
SET [PreApproved] = 'N'
WHERE [CL_TO] - [CL_From] <= 0

UPDATE CLI
SET [PreApproved] = CASE WHEN Acct.Offer_Type_Cd IN ('A','C','Q') THEN 'Y'
WHEN Acct.Offer_Type_Cd = '' THEN 'N'
ELSE NULL END
FROM ##PI_CC_CreditLimitChngAccounts CLI INNER JOIN ##CLIP_Monthly Acct
ON CLI.TSYS_Acct_ID = Acct.Tsys_Acct_ID
AND YEAR(CLI.[Event_CDate]) * 100 + MONTH(CLI.[Event_CDate]) = Acct.Date_Key
AND Acct.Total_Credit_Limit = CLI.[CL_TO]
WHERE CLI.[PreApproved] IS NULL


UPDATE CLI
SET [PreApproved] = 'Y'
FROM ##PI_CC_CreditLimitChngAccounts CLI INNER JOIN ##FICO_CLIP_Response FICO
ON CLI.TSYS_Acct_ID = FICO.Tsys_Acct_ID
AND YEAR(CLI.[Event_CDate]) * 100 + MONTH(CLI.[Event_CDate]) = FICO.Date_Key
AND FICO.Test_Control = 'Test'
AND ([#CLI_Mth0] = 1 OR [#CLI_Mth1] = 1 OR [#CLI_Mth2] = 1)
WHERE CLI.[PreApproved] IS NULL

UPDATE ##PI_CC_CreditLimitChngAccounts
SET [PreApproved] = 'N'
WHERE [PreApproved] IS NULL