I'm working on a personal project and just created my first SSIS package to import flat files into a SQL database but I'm having some difficulties with the results. Here's the issue I'm facing
1. I have a DB1 with empty table1 with the column names defined.
2. I have a delimited csv file with 7 values.
3. I have a SSIS package that imports the csv file above
4. I query the table and i see the values have been imported.
So far everything works well.
5. Now I update file1 and add 2 extra records.
6. File1 now has 7 existing records + 2 new records.
7. I run the SSIS package again, the import works.
8. I query the table and see now that the table has 16 values of which 14 values are the same. This is incorrect.
That means that I'm probably missing a step where i need to check if the value in the table1 already exists and if it does do not import it, instead add only those values that don't exist.
The correct result that i wanted is 7 existing values + 2 new ones, so table1 should now have 9 values.
I've looked around and found that i need to use a transformation lookup, but have not gotten it to work yet or maybe my tasks are not setup correctly.
Can anyone provide any ideas on how to solve this issue or point me to a link that explains how i can fix this.