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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy