Here's the scenario, I'm reading data from an excel spreadsheet(specific format) using SSIS. The package works fine and brings the data into the SQL server database from the spreadsheet. The only issue that I have now is that I need to make sure that two fields that are coming in from the spreadsheet are going to be unique, in other words I (or perhaps I should say the package) should not insert any particular record that happened to have both those fields already in the database.
Now I don't think I can do that kind of stuff in the SSIS package itself. So I was thinking, what if I read everything (without worrying about those two primary keys) and put them into a table (lets call it a temp table in the same database. Then maybe do the validation in the form of a stored procedure (which is what I'm struggling with) and run that stored procedure.
After validating that those 2 fields, lets say ssn and emp ID are unique (are not present in the temp table) then the same stored procedure will some how create the EXACT SAME TABLE with all the constraints and data and put it in another table, which then I can use for reporting.
This is just a wild thought that came to mind. I'm not even sure if this is doable, and if so how?
I'm also open to any other ideas which might make my life a little easier.
Thanks a lot in advance.