I am trying to insert data from an Excel file to a SQL Server database, using SSIS packages. (I have simplified the actual package, to narrow in the problem, so it's like a school book example).
The Excel file has three columns: Name, Product, Supplier.
The database has a table TestTable with three columns: Id (identity int), Name, SupplierId
The SupplierId is mapped to a reference table named Supplier, with columns: Id, Name.
Short description of the SSIS package:
Excel Source --> Sort (on Name) --> Derived Column (adding SupplierId with dummy key 1) --> Lookup (trying to match Supplier names with reference table, replacing SupplierId if found)
If the Lookup finds the supplier, move with a SupplierId. Sort on SupplierId.
If not, insert new suppliers in reference table Suppliers.
** THIS IS WHERE THE PROBLEM IS. **
Lookup to get the SupplierId of the newly inserted suppliers.
Sort on SupplierId.
Merge and Lookup to either insert with OLE DB Destination or update with OLE DB Command.
The problem is:
When making the insert of new suppliers, not only unique suppliers are inserted. If, e.g., the Excel file has two records with supplier "FOX LTD", and the supplier does not yet exist, it adds to records to the reference table. I of course want it to add unique suppliers.
I tried to add a Sort before the insert OLE DB Command, removing rows with duplicate sort values, but then I of course will lose the records that I want to move on with...
This must be an easy thing so solve, but I really can't find out how.
Is there anyone in here who can help me out?
If any code or further explanation is needed, I will provide it.