SSIS Package to insert records only if they dont exist in the table and send the list of new records in an email

Question

Post reply

SSIS Package to insert records only if they dont exist in the table and send the list of new records in an email

compufreak

SSC Eights!

Points: 951
More actions
February 26, 2014 at 1:17 am

#148148

Hi everyone

if possible , can I create a package to send an email that will only show the new records that has been added to my table.

once the new records are captured daily, I need to check if they exist based on a date column and then if they do not exist I will insert them into the table.

I need some assistance in accomplishing this.

also the new records that has been added to the table , I need them to be in the body of an email.

Please assist if possible.

Thanking everyone in advance.:-)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

compufreak (2/26/2014)

Hi everyone

if possible , can I create a package to send an email that will only show the new records that has been added to my table.

once the new records are captured daily, I need to check if they exist based on a date column and then if they do not exist I will insert them into the table.

I need some assistance in accomplishing this.

also the new records that has been added to the table , I need them to be in the body of an email.

Please assist if possible.

Thanking everyone in advance.:-)

What is the primary key of your table ... is it the date column to which you are referring?

Perhaps you could consider adding a column to your table called 'BatchId' (or whatever) which would be populated differently for each package execution? This would allow you to identify when a row was created (assuming you store all your BatchIds in a separate logging table).

Use a Lookup component inside a data flow to check whether a row already exists & direct your 'unmatched' rows to your table. (Note, this works best if there are no duplicates in your source.)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

compufreak SSC Eights! Points: 951 More actions · Answer 2

Hi phil

I have a ID column which is the primary key of my table.

the ID Column can be used to capture a record for the same ID as many times as possible.

so I thought the easiest way to check if the data exists will be based on the date of entry...

everyday data is being entered 🙂

Also I am extremely new to SSIS but I am catching up quiet fast.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

compufreak (2/26/2014)

Hi phil

I have a ID column which is the primary key of my table.

the ID Column can be used to capture a record for the same ID as many times as possible.

so I thought the easiest way to check if the data exists will be based on the date of entry...

everyday data is being entered 🙂

Also I am extremely new to SSIS but I am catching up quiet fast.

Can you explain a bit more please? The bit I don't understand is why you would not match on the PK.

It would help if you could go into more detail about this bit: "the ID Column can be used to capture a record for the same ID as many times as possible."

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

compufreak SSC Eights! Points: 951 More actions · Answer 4

compufreak

SSC Eights!

Points: 951

February 26, 2014 at 1:45 am

#1692163

I have attached a screenshot of my data.

please have a look 🙂

Phil Parkin SSC Guru Points: 245813 More actions · Answer 5

Is that the source or target table? This table contains duplicates and (therefore) I am guessing that it has no PK.

As an aside, Day_Month_Year is not a good choice for a column name. CreateDate (if that is what it is) would be far better, as it gives us an idea of the function of the data.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.