jeudi 28 avril 2016

Synchronization of tables from DB2 to SQL

I had build a T-SQL, that which will sync the tables from DB2 to SQL.We can run the job for every 15 min to sync tables. This query will fetch only the changes that were made from the last job done. But this is the query that which was applicable when the servers are linked. But I don't know, for some reasons my manager want it to be done by SSIS. How can i reach my requirement of syncing data only by fetching the changes from last job done? Is there anyway to modify my query and run it in execute SQL task in SSIS?

BEGIN
IF OBJECT_ID('tempdb..#ELIG_TEMP') IS NOT NULL
DROP TABLE #ELIG_TEMP
UPDATE OPENQUERY ([DB2], 'select h_last_job_end from ISO21.V_LATEST_JOB_CYCLE where C_job = ''ELIG''') 
SET h_last_job_end = current_timestamp;

select * into  #ELIG_TEMP from OPENQUERY([DB2],
'Select * from ISO21.V_USR_ELIG p
where I_USR in (select secid from natb.pyratab p,
ISO21.V_LATEST_JOB_CYCLE  pr
where datetime <= h_last_job_end 
and datetime > h_cur_job_start
and c_job = ''ELIG'')')
MERGE iso21.USR_ELIG 
USING #ELIG_TEMP AS ELIG_TEMP
ON (USR_ELIG.I_USR = ELIG_TEMP.I_USR AND USR_ELIG.C_UNIV_TYP = ELIG_TEMP.C_UNIV_TYP AND USR_ELIG.C_UNIV = ELIG_TEMP.C_UNIV)
WHEN MATCHED THEN
UPDATE
SET USR_ELIG.C_UNIV_TYP = ELIG_TEMP.C_UNIV_TYP,
USR_ELIG.C_UNIV = ELIG_TEMP.C_UNIV                                         

WHEN NOT MATCHED BY TARGET THEN
INSERT (I_USR,C_UNIV_TYP,C_UNIV)
VALUES (ELIG_TEMP.I_USR,ELIG_TEMP.C_UNIV_TYP,ELIG_TEMP.C_UNIV)

WHEN NOT MATCHED BY SOURCE THEN
DELETE;

UPDATE OPENQUERY ([DB2], 'select h_cur_job_start,h_last_job_end from ISO21.V_LATEST_JOB_CYCLE where C_job = ''ELIG''') 
SET h_cur_job_start = h_last_job_end;
END`

Aucun commentaire:

Enregistrer un commentaire