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!

*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.

Jobs

How to read data between 2 servers using tempdb in ssis

How to read data between 2 servers using tempdb in ssis

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

RE: How to read data between 2 servers using tempdb in ssis

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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!

Resources

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