How to read data between 2 servers using tempdb in ssis

[burakcan]

Hello All,
I need to read data from one server and load the data another server by using SSIS and TempTable.
There is no link server between the servers.

I need to load the data into the temptable from server1 table(s) and update the same temptable from another tables in server1 then load the table in server2

In SSIS I created temptable using Execute SQL Task Editor.
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 ##temptable1(
[RN] [int] NULL,
[Date_Key] [int] NOT NULL,
[TSYS_Acct_Id] [bigint] NOT NULL,
[Event_CDate] [Date] NULL,
[SalesBR_To] [int] NULL,
[SALESID_TO] [varchar](10) NULL,
[CL_From] [decimal](18, 2) NULL,
[CL_TO] [decimal](18, 2) NULL,
[CPC] [varchar](3) NULL,
[AcctCurrentPrefix] [int] NULL,
[AcctCurrentRoot] [varbinary](2) NULL,
[AcctCurrentSuffix] [varbinary](2) NULL,
[CC_CL_INCREASE] [varchar](20) NULL,
[SR_Eligible] [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
)

Then in Data flow trying to execute below script in OLE DB Source SQL Command mode.
I have set it up RetainSameConnection property TRUE on TempTable Connection manager .
Also set DelayValidation = TURE on the package property
Still getting error . It does not recognize the ##temptable1

Please see the script below:


INSERT INTO ##temptable1 (
[RN] ,
[Date_Key] ,
[TSYS_Acct_Id] ,
[Event_CDate] ,
[SalesBR_To] ,
[SALESID_TO] ,
[CL_From] ,
[CL_TO] ,
[CPC],
[AcctCurrentPrefix] ,
[AcctCurrentRoot] ,
[AcctCurrentSuffix] ,
[CC_CL_INCREASE] ,
[SR_Eligible] ,
[EDW_P_CUST_ID] ,
[EDW_B_CUST_ID] ,
[Employee_Key] ,
[Channel_Key] ,
[Product_Key]
)
(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY CL.TSYS_Acct_ID, CL.[Event_CDate] ORDER BY CL.TSYS_Acct_ID)
,YEAR(CL.[Event_CDate])*10000+MONTH(CL.[Event_CDate])*100+DAY(CL.[Event_CDate]) AS [Date_Key]
,CL.[TSYS_Acct_ID]
,CL.[Event_CDate]
,SB.[SalesBR_To]
,SO.[SalesID_To]
,CL.[CL_From]
,CL.[CL_To]
,CL.[CPC]
,c.[Current_Prefix] AS [AcctCurrentPrefix]
,c.[Current_Root] AS [AcctCurrentRoot]
,c.[Current_Suffix] AS [AcctCurrentSuffix]
,CASE WHEN CL.[CL_From] < CL.[CL_To] THEN 'CL_INCREASED'
WHEN CL.[CL_From] > CL.[CL_To] THEN 'CL_INCREASED'
ELSE 'NO_CL_CHANGES' END AS [CC_CL_INCREASE]
,CASE WHEN SB.TSYS_ACCT_ID IS NULL THEN 'N' ELSE 'Y' END [SR_Eligible]
,cus.P_CUST_ID AS [EDW_P_CUST_ID]
,cus.B_CUST_ID AS [EDW_B_CUST_ID]
, NULL AS [Employee_Key]
, NULL AS [Channel_Key]
, NULL AS [Product_Key]
FROM [BIM_DAILY].[dbo].[EVENT_DAILY_110] CL with (nolock)
LEFT JOIN [BIM_DAILY].[dbo].[EVENT_DAILY_27441] SB with (nolock)
ON CL.TSYS_Acct_ID = SB.TSYS_Acct_ID
AND CL.[Event_CDate] = SB.[Event_CDate]
AND ABS(CONVERT(INT, CL.[Event_Time]) - CONVERT(INT,SB.[Event_Time])) < 50
AND SB.SALESBR_TO <> ''
LEFT JOIN [BIM_DAILY].[dbo].[EVENT_DAILY_27481] SO with (nolock)
ON CL.TSYS_Acct_ID = SO.TSYS_Acct_ID
AND CL.[Event_CDate] = SO.[Event_CDate]
AND ABS(CONVERT(INT, CL.[Event_Time]) - CONVERT(INT,SO.[Event_Time])) < 50
AND SO.SALESID_TO <> ''

LEFT JOIN
(SELECT [Date_Key]
,[Tsys_Acct_ID]
,[Current_Prefix]
,[Current_Root]
,[Current_Suffix]
,[Relationship_Status_Cd]
,[Cust_Relationship_Type_Cd]
FROM [BIM].[dbo].[CC_CUSTOMER]
UNION
SELECT
[Date_Key]
,[Tsys_Acct_ID]
,[Current_Prefix]
,[Current_Root]
,[Current_Suffix]
,[Relationship_Status_Cd]
,[Cust_Relationship_Type_Cd]
FROM [BIM_DAILY].[dbo].[CC_CUSTOMER_CURRENT]
) c
ON CL.[TSYS_Acct_ID] = c.TSYS_Acct_ID
AND ISNULL((YEAR(CL.[Event_CDate])*100+MONTH(CL.[Event_CDate])),'209901') = ISNULL(c.[Date_Key],'209901')
LEFT JOIN [BIM].[dbo].[CC_ACCT_CUST] cus ON CL.[TSYS_Acct_ID] = cus.[TSYS_Acct_ID] AND cus.To_Dt IS NULL
WHERE CL.[TSYS_Acct_ID] > 0
AND CL.[Event_CDate] >= '2015-11-01 00:00:00'

) ORDER BY CL.[TSYS_Acct_ID]

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

DELETE FROM ##temptable1 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 ##temptable1 CL
INNER JOIN [BIM_DAILY].[dbo].[CC_SR_DAILY] SR with (nolock)
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 ##temptable1 CLI INNER JOIN [BIM_DAILY].[dbo].[CLK_CREDIT_APP_DAILY] CLK with (nolock)
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


Any help greatly appreciated.

 

[fredericofonseca]

my advise is to use a c# component as a source of the data flow - there you can use a standard sql client to do all your create table, joins and so on, add each row to the output buffer and let it flow onto the final (permanent) table.

SSIS and temp tables, even global temp tables, do not get along - best to avoid them althogether

as another note get ride of those nolock unless the business have stated that they do not care that their financial data is incorrect.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886