June 17, 2016 at 7:13 am
Okay so I am having trouble figuring out how to handle data compare in SSIS.
I have two tables in two separate databases on the same server that have matching data, one is used for a vendor app, the other is used for an in house application. The database that is the backend for the in house application is updated daily. These tables store data for when a user terminates employment.
What I need to do is a daily compare to see what new termination dates came through over night. So my SSIS package goes out, does a table compare using an except statement. Finds the count, if the count is >0 then proceed.
The next step is to grab all of the new terminations and place them in a flat file for the HR department. This is where I am having trouble, how do I get SSIS to compare two tables based on the Employee ID and the Termination Date. I wrote a script using temp tables that works until I try to run it as a SQL task. It doesn't like the fact that the table isn't created.. Should I just create a table that exists out there all the time and truncate each time the job is run? Or will an SSIS merge join actually do the trick? If merge join is the best way, can someone please explain how to use it or provide some info on how to use it?
Here is the code that works to pull back the newly terminated users.
CREATE TABLE #temp (LastName varchar(300),
FirstName varchar(300),
TermDate datetime,
EmpNumber varchar(100),
Division varchar(300) NULL,
Supervisor varchar(500) NULL,
WhenTerm varchar(500) NULL
)
INSERT INTO #temp
SELECT LastName, FirstName, TermDate, EmpNumber, NULL, NULL, NULL
-- The null null null is added as a place holder until the update statements execute.
FROM Database_B.dbo.EmpTerms_Input -- Updated nightly
WHERE TermDate is NOT NULL
EXCEPT
SELECT LastName, FirstName, TermDate, EmpNumber, NULL, NULL, NULL
FROM Database_A.dbo.EmpTerms
WHERE TermDate is NOT NULL
UPDATE #temp
SET Division = (SELECT e.Division FROM Database_B.dbo.EmpTerms_Input e WHERE #Table_1.EmpNumber = e.EmpNumber)
UPDATE #temp
SET Supervisor = (SELECT e.Supervisor FROM Database_B.dbo.EmpTerms_Input e WHERE #Table_1.EmpNumber = e.EmpNumber)
UPDATE #temp
SET WhenTerm = CASE
WHEN TermDate <= (GETDATE()) THEN 'Term Immediately'
WHEN TermDate <= (GETDATE() + 7) THEN 'Term As of Term Date'
ELSE 'Contact Human Resources'
END
SELECT * FROM #temp
DROP TABLE #temp
Thanks!
June 17, 2016 at 8:14 am
Should I just create a table that exists out there all the time and truncate each time the job is run?
Yes, that an easy and robust way.
Or will an SSIS merge join actually do the trick?
As a general rule, joins in SSIS are slower than joins in SQL, so I would advise against this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2016 at 11:30 am
Phil,
Thank you very much for the advice, I used SQL queries in my SSIS package instead of a merge join and created a static table to be truncated each morning.
Thanks again!
June 17, 2016 at 12:08 pm
cstg85 (6/17/2016)
Phil,Thank you very much for the advice, I used SQL queries in my SSIS package instead of a merge join and created a static table to be truncated each morning.
Thanks again!
No problem. Sounds like you've got this nailed, well done.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply