SSIS Lookup No Match Output

[uvil]

Good Afternoon, 

I just started to work with Microsoft Fabric a few days ago. In General it's all so intuitive and quite easy to understand and to discover. After some days trying and doing things I wanted to replicate one SSIS package that we use every night to copy all the works done in the day and insert them in our DataWareHouse. 

The thing is that i'm not able to see the Lookup object of SSIS, that I was using. The function of this Lookup is so easy. Every night I do a Select statement where I get all the works of the last 30 days, as it's possible to enter some jobs manually in SSIS I can do a lookup with No Match Output where inside them I can check if the row key is inside or no, and insert it if necessary. As some employees can insert manually a job made 5 days ago because the mobile app was down or whatever. 

In the Dataflow object, i'm not able to see any thing that can help me to avoid inserting duplicates as when I do the select I look 30 days back, just to recover manual introductions, 

¿Is there any way to do this, or I need to change my way of think on this process?

For the moment I see that SSIS is more complete than ADF in this terms... Maybe i'm losing something, 

Any help is welcomed, 

Best Regards,.

[frithjof_v · Accepted solution]

I'm not sure I followed everything in your description, but I am guessing you want to use the Dataflow Gen2 to insert only new rows into the Data Warehouse.

If so, I think it can be done the following way:

Bring in both your source system ("jobs") and the data warehouse table as two separate queries into your Dataflow Gen2 (i.e. Get Data).

Filter the data from the data warehouse table to only query the last 30 days (as per your description).

Next, I am assuming you have a Job ID column or another column/set of columns which uniquely identify each row.

Do an anti join between your source system query and the data warehouse query. The anti join will ensure that any rows in the source system query, which are already in the warehouse, will be filtered out of the source system query. 

Then write the remaining rows to the data warehouse table. Use the warehouse table as the Data destination and choose Append method.

[frithjof_v · Accepted solution]

I'm not sure I followed everything in your description, but I am guessing you want to use the Dataflow Gen2 to insert only new rows into the Data Warehouse.

If so, I think it can be done the following way:

Bring in both your source system ("jobs") and the data warehouse table as two separate queries into your Dataflow Gen2 (i.e. Get Data).

Filter the data from the data warehouse table to only query the last 30 days (as per your description).

Next, I am assuming you have a Job ID column or another column/set of columns which uniquely identify each row.

Do an anti join between your source system query and the data warehouse query. The anti join will ensure that any rows in the source system query, which are already in the warehouse, will be filtered out of the source system query. 

Then write the remaining rows to the data warehouse table. Use the warehouse table as the Data destination and choose Append method.

[uvil · Accepted solution]

Good Afternoon, 

Yes, I finally did it inside the Dataflow, I made it as you said, 

1 - Via PowerQuery, in my case it was easier with the Advanced Editor I created a Variable Destination with the select of just the keys that are in the Destination

2 - I did a filtering doing the anti-Join, so PowerQuery just returns me the new data. 

3 - On the data destination section I chosed Append, so I insert the rows. 

Doing this it works, it's a bit complex than before, but it does the job, thanks for the help, 

Best Regards,.