|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2012-01-11 : 18:40:35
|
| How do I compare the records of one database with the records of a second database in ssis?I am pulling from one database table...but want to compare the results of a field with the same field in a second database table..if the values exist in that second database table, then I will throw out those records...This is a conversion from a .dts script where I was using nested queries...see the last couple lines below, where I check to see if the esrnumber exists in that other database...(so far, in ssis I have an oledb source read, green line to conditional split based on the where clause, green line to a derived column where I set some new fields....but where do I filter for the check that a record already exists in that other database?INSERT INTO ESRWorkRequest (ESRNumber, PIN, Summary, [Description], StatusCode, Created, Team, Agency, [Type], ESRAction, ITCoordinator, ITCoordinatorPhone, AgencyContact, AgencyContactPhone, HasAttachments, Updated, UpdatedBy )SELECT * FROM OPENROWSET ('SQLoledb', 'mydatabase';'SQLID';'PW','SELECT Change_ID_ as ESRNumber, EPM_PIN as PIN, Summary, [Description], Status as StatusCode, DATEADD(s, Create_Date, ''1970-01-01 00:00:00'') as Created, Supervisor_Group_ as Team, Region as Agency, ''EPM Milestone'' as Type , ''New'' as ESRAction, Requested_By_Name as ITCoordinator, Requested_By_Phone as ITCoordinatorPhone, Requester_Name_ as AgencyContact, Phone_Number as AgencyContactPhone, (CASE WHEN Attachment_1 <> ''''THEN 1 WHEN Attachment_2 <> ''''THEN 1 WHEN Attachment_3 <> ''''THEN 1 WHEN Attachment_4 <> ''''THEN 1 WHEN Attachment_5 <> ''''THEN 1 WHEN Backout_Plan <> ''''THEN 1 WHEN Implementation_Plan <> ''''THEN 1 WHEN Test_Plan <> ''''THEN 1 ELSE 0 END) as HasAttachments, getDate() as Updated, ''System'' as UpdatedBy FROM CHG_Change with(nolock) WHERE Status not in (6,7) AND Change_Type = 4 ' ) WHERE ESRNumber NOT IN (SELECT ESRNumber FROM ESRWorkRequest) |
|
conditional split based on second table