November 28, 2011 at 6:46 am
I am working on migrating from Rowhash bound SSIS packages to Change Tracking SSIS packages to load my data warehouse. The ETL process is pretty standard, I pull data from our OLTP, stage the data in ETL db then load in EDW. The issue that I am running into is change_tracking_current_version(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'table')) and CHANGETABLE(CHANGES table, @last_csn) must be called from the source db. I really don't want to store my SSIS extract stored procedures on the production system. I have another db on the same box or another box alltogether that I would prefer storing them on.
How can I call the CT functions cross database?
change_tracking_current_version(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'table')) and CHANGETABLE(CHANGES table, @last_csn)
I tried setting up a generice store procedure to call cross db that calls the functions. That worked from change_tracking_current_version(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'table')) but would not work for this one CHANGETABLE(CHANGES table, @last_csn)
Any ideas or direction would greatly be appreciated. Below is a sample of my extract proc that works perfect on the source db. Just need to get the CT function calls to work correctly cross db.
ALTER PROCEDURE [dbo].[SSISStageApptRepExtract] ( @last_csn INT )
AS
DECLARE @csn INT = change_tracking_current_version()
DECLARE @min_csn INT = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'ApptRep'))
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;
BEGIN TRANSACTION
--Make sure the last_sync_version is valid
IF ( @last_csn < @min_csn )
BEGIN
SELECT TOP 1
ar.LeadID ,
ar.ApptDate ,
ar.SalesRepID ,
ar.CreditDivisor ,
BINARY_CHECKSUM(ar.LeadID, ar.ApptDate, ar.SalesRepID,
ar.CreditDivisor) AS RowHash ,
1 AS AuditKey ,
ar.ApptRepID ,
GETDATE() AS LoadDate ,
'I' AS CO ,
'f' AS LoadType
FROM leads.dbo.ApptRep ar
WHERE ar.ApptDate >= '20000102'
END
ELSE
BEGIN
SELECT ar.LeadID ,
ar.ApptDate ,
ar.SalesRepID ,
ar.CreditDivisor ,
BINARY_CHECKSUM(ar.LeadID, ar.ApptDate, ar.SalesRepID,
ar.CreditDivisor) AS RowHash ,
@csn AS AuditKey ,
t.ApptRepID ,
GETDATE() AS LoadDate ,
t.CO ,
'p' AS LoadType
FROM ( SELECT ct.ApptRepID ,
CT.SYS_CHANGE_VERSION CV ,
CT.SYS_CHANGE_OPERATION CO ,
@csn AS CSN
FROM CHANGETABLE(CHANGES dbo.ApptRep, @last_csn) AS CT
) t
LEFT JOIN leads.dbo.ApptRep ar ON t.ApptRepID = ar.ApptRepID
WHERE ar.ApptDate >= '20000102'
END
Thanks
Bryan
August 6, 2013 at 5:43 am
Did you ever get a resolution to this? I have a SSIS package I'm working on that uses change tracking and I need to combine several tables at the source into a single table at the destination. Trouble is, the source tables aren't all in the same database...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply