CODEWITHSUNDEEP
sqlsql-serveroracle-databasessisoracle12c
PKN
PKN

Reputation: 31

Need SSIS Help - How to Get the ID from Destination

I am new to SSIS. Currently working on migrating data from SQL to Oracle. We have tables in both the databases (with different Datatypes). Say, one master (Roles) and one child (Employee). I am migrating Employee table from SQL to Oracle. RoleID is the FK in Employee table.

Since the Destination datatype & RoleIds are different from Source, I need to get the "RoleName" from Source employee table, connects with Role Table in Destination and get the respective IDs.

Source: Employee Table --> RoleId (GUID Datatype)

Destination Employee Table --> RoleId (Int Datatype)

I tried to use FuzzyLookup and Lookup, I dont get the destination Connection Manager details there. Any other idea how to achieve this?

Thanks in advance.

Table Structure:

enter image description here

Upvotes: 3

Views: 468

Answers (2)

Hadi
Hadi

Reputation: 37348

Use a normal Lookup Transformation

Why using Fuzzy Lookup Transformation while you can achieve this using a normal Lookup transformation.

The Fuzzy Lookup transformation has a limitation since the reference must be an SQL Server database, from the Fuzzy lookup transformation documentation:

The reference data source must be a table in a SQL Server database.

Based on the Lookup transformation documentation:

The Lookup transformation supports the following database providers for the OLE DB connection manager:

  • SQL Server
  • Oracle
  • DB2

Data preperation

If the data are not the same, which was the cause to use Fuzzy lookups, then try cleaning and preparing your data to match the lookup reference. There are many approach:

  1. Data preparation (wrangling) using SQL
  2. Using Derived Columns for simple cleaning operations
  3. Using a Script Component for a complex cleaning logic
  4. Using Data Conversion transformation to match the Data Types

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

The best way to handle this, if you are able, is to import the Source data as-is into a staging table on the Destination server, and then import the data to the final table in a procedure on the Destination server that does a lookup in the procedure code to get the IDs.

If you can't do that and have to do it all in the SSIS package, then you can do this by adding another Source component to your dataflow that gets the data from the Role table in Oracle and does a Merge Join to the data coming from SQL Server, joining on the RoleName to get the ID from the Oracle table into the dataflow. Note that if you do it this way, you will have to ORDER both source components on the Merge Join key (RoleName).

Upvotes: 1

Related Questions