2

I created an SSIS Pacakge that imports a monthly extract claims file. I have a simple Data Flow task, flat file to OLEDB Destination with a Derived column component in between used to generate a primary Key (based on a combination of 3 columns in the extract file).

What I would like to do if possible is have the SSIS package only import brand new records based on the primary key so that if there is a file that is being imported that contains a (generated) primary key that already exists in the destination table, then I would like the package to delete the existing record in the table and then add the new record from the current file being imported.

I know I could do this by having a staging table where I load the file and then using some sql queries (sp) to delete and add records to the table appropriately but I am trying to accomplish the import without needing to create a staging table because the table that I am currently populating, already is a staging table...

Is what I am trying to accomplish possible, using SSIS and only 1 destination table or will I need to create an additional staging table?

Improve this question
2
  • 2
    You want to look into Lookup transformation for what you want to achieve. Also see msdn blog for an example.
    – Kin Shah
    Jul 31, 2015 at 21:13
  • 1
    To follow up with what Kin said, you can use a Lookup transformation along with an OLE DB command to do the delete...However, there are some issues with using that OLE DB Command (as Devin Knight mentions here sqlservercentral.com/blogs/dknight/2008/12/29/… ). Long story short, I am not sure your initial approach of loading to a staging table is a bad one, depending on your situation. But it is certainly possible to do what you are wanting with these tools.
    – mskinner
    Jul 31, 2015 at 21:31

1 Answer 1

Reset to default
0

As Kin mentioned in his comment above, it is possible to accomplish what I was trying to do using the SSIS Lookup transformation component (example also provided in his comment) which answers my question.

While I will surely be using this component in the future I decided to go with a more straightforward SQL scripting method to accomplish what I needed after reading mskinner's comment (and referenced blog) which points out that using an Execute SQL task is probably going to be much faster/efficient than using the Lookup Transformation component in SSIS for what I am trying to accomplish.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.