Incremental load package design
Hi I built 2 packages
1) with merge join
2)lookups
for incremental load both are inserting duplicate rows in to my destination
I used left outer join in my look up with the detination with two columns in join conditions patientID and EpisodeNum
than later i used a conditional split to slip the values
for new inserts ISNULL(Inc_AEAttendance_Id_d)
updates
ISNULL(Inc_AEAttendance_Id_d) && (PatientID) != (PatientID_d) || (EpisodeNum) != (EpisodeNum_d) || (AttendanceDate) != (AttendanceDate_d) || (AEFollowUpMF_Code) != (AEFollowUpMF_Code_d) || (AEInitiatorMF_Code) != (AEInitiatorMF_Code_d) || (AEModeOfArrivalMF_Code)
!= (AEModeOfArrivalMF_Code_d) || (AENumber) != (AENumber_d) || (AmbulanceNum) != (AmbulanceNum_d) || (FollowUp) != (FollowUp_d) || (DepartureDate) != (DepartureDate_d)
the package run fine but the updated values were wrong it inserted duplicate rows in to my destination is there any suggestions or best design process to update the column values ?
Sri.Tummala
May 15th, 2012 10:14am
I typically do that using Lookup Transformation + the Split Component,
no joins, similar to what has been described here: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
In your case, it seems that the join is at fault. Specifically, NULLs are coming as a result of the LEFT JOIN I guess. So these are the new records that need to be inserted, but I still suggest to review your design against the link I provided.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 10:32am
I changed my updates condition to this i just cut down all my destination columns updates to just patientid and episodenumber is that a right method of doing this
should i include all the columns which are in the destination .
The patientid and the episodenumber are the columns that are used in lookup condition to join left outer join with the destination and the source .
ISNULL(Inc_AEAttendance_Id_d) && (PatientID) != (PatientID_d) || (EpisodeNum) != (EpisodeNum_d)
the condition which i used in past was
ISNULL(Inc_AEAttendance_Id_d) && (PatientID) != (PatientID_d) || (EpisodeNum) != (EpisodeNum_d) || (AttendanceDate) != (AttendanceDate_d) || (AEFollowUpMF_Code) != (AEFollowUpMF_Code_d) || (AEInitiatorMF_Code) != (AEInitiatorMF_Code_d) || (AEModeOfArrivalMF_Code)
!= (AEModeOfArrivalMF_Code_d) || (AENumber) != (AENumber_d) || (AmbulanceNum) != (AmbulanceNum_d) || (FollowUp) != (FollowUp_d) || (DepartureDate) != (DepartureDate_d)
if i include all these columns the update is not performing good it is just duplicating vlaues in my destination.
Sri.Tummala
May 15th, 2012 10:32am
Are your duplicate values in the same case as the originals? Be aware that the logic you are using is case sensative.Chuck
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 10:34am
Do i need to update the defaultconditional split values to my destination ?Sri.Tummala
May 15th, 2012 10:37am
what are they? What do you get as input, what do you get inserted? Any data examples?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 11:04am
sorry Arthur its been sorted now the lookup condition should be with three columns patinent,episodenumber and attandence data i will mark you as answer can you help me with change data caputre using ssis our business needs a live data warehouse using change
data capture can you suggest me good sources to implement this
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9c74fc19-f273-41ec-a972-2f10a26f09b1Sri.Tummala
May 15th, 2012 11:21am
Thank you.
I have just asked you a number of questions to start off on the other thread.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 11:30am