SSIS import data or insert data if no match

etlimportssis

I have a situation in which I need to import data from one table to another. The challenge here is in tables I have some columns which are references to other table values (kind of foreign key). I need to match these referencing columns and if fields match I need to map the respective id otherwise I need to insert that value in the referencing table and than map id in the source table data source and import all the data.

Elaborating my problem:

ETL with Lookups

So this is how my ETL look like. Currently I am "On Lookup Match" moving forward with execution but I also need to create new records in the respective table if "Look up don't match". (And this I need to do for all the 8 lookups I am doing). I do not understand how to do all this in sequence and import all the data at final stage.

Best Answer

Here is what i did:

Created a new data flow to look up and insert non matching fields.
Inserted the actual data flow.

Related Solutions

Sql-server – Advice on how to approach data cleaning & importing

I think the biggest challenge will be doing the event type transformations efficiently, so I'm going to focus on that and ignore the schema completely.

My first thought (read: I've never actually tried this) is to use dynamic SQL to build up a SELECT statement that will do the data transformation such that values end up in their correct columns (i.e., Col0 --> CompanyId, or whatever). This boils down to a mapping table consisting of the event type, the source column, the target column, and a default value if required.

Once you have the mapping table (or tables, depending on how complex this actually is), you can use it to dynamically build a SQL statement of the form:

SELECT
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col0, 'DefaultCategory')
        END
    ) AS Category,
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col1, 'DefaultCompany')
            WHEN 5000 THEN COALESCE(Col0, 'DefaultCompany')
        END
    ) AS Company,
    (
        CASE EventType
            WHEN 5000 THEN COALESCE(Col1, 'DefaultLocation')
        END
    ) AS Location
    FROM RawSourceTable

Build the statement as part of the ETL process. If you need to add a new event type, just add the required column mappings to the mapping table, and they'll be handled the next time the ETL process runs.

Once the source data is in this kind of state, the rest of the import process should be pretty standard.