Search code examples
ssisbusiness-intelligencebids

SSIS Data Flow: duplicated rule problem after lookup

I have a data flow that I need to get a column value from 'SQL tableA' and do a lookup task in 'SQL tableB' using this column value. If the lookup found a connection between the two tables, I need to get the value of another column from 'SQL tableA' and put this value in 'SQL tableC'( the table that will be persisted ). If lookup fail, this column value will be NULL.

My problem: After this behavior above, the rest of my flow is the same. So I have two duplicated equal flows below lookup. And this is terrible for readability and maintenance.

What do I can do to resolve this situation with little performance loss? The data model is legacy, so change the data model is impossible.

enter image description here

Best Regards, Luis

Solution

  • The way I see it, there are really three options:

    1. Use UNION ALL and possibly sacrifice performance for modularity. There may in fact be no performance issue. You should test and see

    2. If possible, implement all of this in a stored procedure. You can implement code reuse there and it will quite possibly run much faster

    3. Build a custom transformation component that implements those last three steps. This option appeals to all programmers but may have the worst performance and in my opinion will just cause issues down the track. If you're writing reams of C# code inside SSIS then you'll eventually reach a point where it's easier to just build a standalone app.

    It would be much easier to answer if you explained

    • What you're really doing
      • slowly changing dimension?
      • data cleansing?
      • adding reference data?
      • spamming

    • What are those three activities?

      • sending an email?
      • calling a web service?
      • calling some other API?

    • What your constraints are

      • Is all of this data on one server and can you create stored procs and tables?