×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Loading and Updating Temp tables in SSIS

Loading and Updating Temp tables in SSIS

Loading and Updating Temp tables in SSIS

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close