2

I have 2 tables Employee and Department tables

Employee : (SQL Server)

EmpID EmpName DOJ
1      Mohan      NULL
2      Manasa     NULL

Department : (Teradata)

DepID     DepName EmpName        DOJ
1        Maths    Mohan K       2017-08-01
2        English  Mohan Kumar 2018-08-01
3        Science  Manasa K    2016-08-01
1        Social   Manasa       2017-09-01

Where I need to update Employee table Date of joining column using the Department table. I need to get latest date of joining basing on the MAX Condition and need to use LIKE Condition using SSIS.

Initially I have used EXECUTE SQL TASK and sending the FULL RESULT SET through variable and putting that SQL TASK into For Each loop container

And need to update in the Employee table like below :

EmpID  EmpName   DOJ
1         Mohan   2018-08-01
2         Manasa  2017-09-01

I have used

  • EXECUTE SQL TASK : Where I have written query SELECT EMPNAME FROM EMPLOYEE and given full result set and stored in a variable
  • FOR EACH LOOP CONTAINER : used ADO.ENUMERATOR and used that variable .

Please suggest a way to achieve this

Employee table is from SQL Server and Department table is Teradata

Hadi
  • 36,233
  • 13
  • 65
  • 124
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • 2
    What logic are you using to match `EmpName` values in the `Employee` table to those in the `Department` table? What happens when two employees have the same first or last name? – digital.aaron Feb 05 '19 at 19:06
  • How may rows in Department and how many rows to be updated in Employee? – dnoeth Feb 06 '19 at 07:51

1 Answers1

1

(1) If Source and Destination are on the same server

I think you can achieve this using an Execute SQL Task without a Foreach Loop container:

(a) Employee.EmpName = Department.EmpName

With CTE_1 AS (SELECT EmpName  , MAX(DOJ) as mx FROM Department GROUP BY EmpName) 
    UPDATE T1
    SET T1.DOJ = CTE_1.mx
    FROM Employee T1 INNER JOIN CTE_1 ON CTE_1 EmpName = T1.EmpName;

(b) Employee.EmpName = Department.FirstName

If the Department table contains a full name and Employee table contains only first name you should use the following query:

With CTE_1 AS (CASE WHEN CHARINDEX(' ', EmpName) > 0
               THEN SUBSTRING(EmpName, 1, LEN(EmpName) - CHARINDEX(' ', REVERSE(EmpName)))
               ELSE EmpName END AS   EmpName  
     , MAX(DOJ) as mx FROM Department GROUP BY CASE WHEN CHARINDEX(' ', EmpName) > 0
               THEN SUBSTRING(EmpName, 1, LEN(EmpName) - CHARINDEX(' ', REVERSE(EmpName)))
               ELSE EmpName END) 
    UPDATE T1
    SET T1.DOJ = CTE_1.mx
    FROM Employee T1 INNER JOIN CTE_1 ON CTE_1 EmpName = T1.EmpName;

(c) Department.FirstName Begins with Employee.EmpName

Or if you are looking to join using a begin with condition which is less accurate:

With CTE_1 AS (SELECT EmpName  , MAX(DOJ) as mx FROM Department GROUP BY EmpName) 
    UPDATE T1
    SET T1.DOJ = CTE_1.mx
    FROM Employee T1 INNER JOIN CTE_1 ON CTE_1 EmpName LIKE T1.EmpName + '%';

(2) If Source and Destination are on different servers

(a) Create a Linked Server in SQL SERVER Management Studio

I think that the simplest way is to create a linked server in SSMS or by T-SQL for the Teradata database and perform on of the queries above.

(b) Use a staging table

Another way, is to import the department table to a temporary table in SQL Server, then perform one of the queries above.

(c) Use Lookup with an OLEDB Command

If you don't have permissions to create a linked server, or to import data to a temporary table, you can an OLE DB Source from department table with a similar command:

SELECT CASE WHEN CHARINDEX(' ', EmpName) > 0
           THEN SUBSTRING(EmpName, 1, LEN(EmpName) - CHARINDEX(' ', REVERSE(EmpName)))
           ELSE EmpName END AS   EmpName  
 , MAX(DOJ) as mx FROM Department GROUP BY CASE WHEN CHARINDEX(' ', EmpName) > 0
           THEN SUBSTRING(EmpName, 1, LEN(EmpName) - CHARINDEX(' ', REVERSE(EmpName))) ELSE EmpName END From Department

And use an OLEDB COMMAND (SQL Server connection) to perform the update operations row by rows, using a similar command:

Update Employee SET DOJ = ? WHERE EmpName = ?

And in Column Mappings Tab map the DOJ column to the first parameter and EmpName column to the second parameter. if you are new to OLE DB Command check the following example for more details.

References

Hadi
  • 36,233
  • 13
  • 65
  • 124