Create an SSIS package to append new records in a table

Question

Post reply

Create an SSIS package to append new records in a table

pwalter83

SSChampion

Points: 14575
More actions
February 23, 2011 at 5:29 am

#388917

Hello Friends,
I have a business requirement to create an SSIS package that is triggered whenever a new record is added to the table in the database.
What the SSIS package should do is to append the new record to the records in the equivalent table.
Thanks and Regards,
Paul

Viewing 15 posts - 1 through 14 (of 14 total)

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

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

Where is this 'equivalent table'?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

pwalter83 (2/23/2011)
Hello Friends,
I have a business requirement to create an SSIS package that is triggered whenever a new record is added to the table in the database.
What the SSIS package should do is to append the new record to the records in the equivalent table.
Thanks and Regards,
Paul

Isn't it much much easier to use a trigger on the table that will write the rows to the "equivalent table" using the INSERTED table?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

What is the insert volume of the table, as you really dont want to be initiating an SSIS package on every Insert if theres a high volumne of Inserts, as you would get a lot of overlap.

I would suggest using a trigger IF the databases are on the same server.

If they're on different servers then you might be better setting seperate table that just contains ID's of the rows added since the last batch, and a moved flag.

Using an Insert Trigger to keep this updated with new row id's, then have an SQL Agent job that polls this table every so often (10-15 minutes), which then pushes the new records through to the destination table.

Its a very crude but effective version of CDC, dont forget that once you have Loaded batch of records you simply update the flag on the trigger table to say they are loaded, not forgetting to clear it down once a day.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Hi Phil,

Thanks for your reply.

The equivalent tables are on a different server. So basically, the scenario is that when the records are added to the tables in the database on server 1 , then the SSIS package on server 1 should append the same records to the existing records in database on server 2.

Thanks,

Paul

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

pwalter83 (2/23/2011)
Hi Phil,
Thanks for your reply.
The equivalent tables are on a different server. So basically, the scenario is that when the records are added to the tables in the database on server 1 , then the SSIS package on server 1 should append the same records to the existing records in database on server 2.
Thanks,
Paul

In that case, wouldn't it be easier to implement replication, mirroring or log shipping instead of re-inventing the wheel?

In my opinion, log shipping is the easiest method.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pwalter83 SSChampion Points: 14575 More actions · Answer 6

In that case, wouldn't it be easier to implement replication, mirroring or log shipping instead of re-inventing the wheel?
In my opinion, log shipping is the easiest method.

Hello Koen,

Thanks for your replies. Actually I am quite new to SSIS and am having a little difficulty understanding your solution. Would it be possible to explain what do you mean by log shippng ?

Thanks and Regards,

Paul

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Log shipping (it has nothing to do with SSIS):

http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx

http://msdn.microsoft.com/en-us/library/ms190640.aspx

http://msdn.microsoft.com/en-us/library/ms187103.aspx

This is more work for a DBA then for a developer (as are the other solutions I mentioned).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pwalter83 SSChampion Points: 14575 More actions · Answer 8

Koen Verbeeck (2/23/2011)
Log shipping (it has nothing to do with SSIS):
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
http://msdn.microsoft.com/en-us/library/ms190640.aspx
http://msdn.microsoft.com/en-us/library/ms187103.aspx
This is more work for a DBA then for a developer (as are the other solutions I mentioned).

Hi Again,

So is Log shipping the only solution to this or can I make use of another solution as a developer. I don't know where I am going with this.

Thanks,

Paul

Phil Parkin SSC Guru Points: 246977 More actions · Answer 9

Did you take a look at Jason's suggestion? It might work for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pwalter83 SSChampion Points: 14575 More actions · Answer 10

Phil Parkin (2/25/2011)
Did you take a look at Jason's suggestion? It might work for you.

I studied Jason's solution but it doesnt seem to be according to the business requirement in terms of updating the flag and then clearing it down once a day. The process needs to be automated and should not include anything which should be done manually everytime. Is there some other different way by just using SSIS ? I am really stuck on this one now.

Thanks and regards,

Paul

Phil Parkin SSC Guru Points: 246977 More actions · Answer 11

pwalter83 (2/25/2011)
Phil Parkin (2/25/2011)
Did you take a look at Jason's suggestion? It might work for you.
I studied Jason's solution but it doesnt seem to be according to the business requirement in terms of updating the flag and then clearing it down once a day. The process needs to be automated and should not include anything which should be done manually everytime. Is there some other different way by just using SSIS ? I am really stuck on this one now.
Thanks and regards,
Paul

I do not agree with your assessment - try re-reading it. Once it is set up, a SQL Agent job takes care of everything without manual input.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pwalter83 SSChampion Points: 14575 More actions · Answer 12

I do not agree with your assessment - try re-reading it. Once it is set up, a SQL Agent job takes care of everything without manual input.

Okay Phil, I have read the solution again. Could you please explain what is meant by :

1. Setting separate table that contains ID's- Does he mean primary key column by this ?

2. Moved flag ?

3. Insert trigger

I am quite new to Sql server and facing difficulty understanding this. Would it also be possible for you to construct a sample query based on what Jason mentioned ?

Thanks,

Paul

Phil Parkin SSC Guru Points: 246977 More actions · Answer 13

pwalter83 (2/25/2011)
I do not agree with your assessment - try re-reading it. Once it is set up, a SQL Agent job takes care of everything without manual input.
Okay Phil, I have read the solution again. Could you please explain what is meant by :
1. Setting separate table that contains ID's- Does he mean primary key column by this ?
2. Moved flag ?
3. Insert trigger
I am quite new to Sql server and facing difficulty understanding this. Would it also be possible for you to construct a sample query based on what Jason mentioned ?
Thanks,
Paul

Sorry Paul, I do not have time to provide a detailed answer. But, in short:

1) Yes

2) A Boolean field, Yes if data already moved to new table, No/Null otherwise.

3) You should read up on this. A trigger is a procedure defined against a table which will fire when an event occurs. These events can be inserts, updates, deletes and others (a different trigger can be defined for each event). So, in this case, the trigger can detect that new data has been inserted to the table and react by inserting the newly created primary keys into your new tracking table.

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pwalter83 SSChampion Points: 14575 More actions · Answer 14

Jason-299789 (2/23/2011)
What is the insert volume of the table, as you really dont want to be initiating an SSIS package on every Insert if theres a high volumne of Inserts, as you would get a lot of overlap.
I would suggest using a trigger IF the databases are on the same server.
If they're on different servers then you might be better setting seperate table that just contains ID's of the rows added since the last batch, and a moved flag.
Using an Insert Trigger to keep this updated with new row id's, then have an SQL Agent job that polls this table every so often (10-15 minutes), which then pushes the new records through to the destination table.
Its a very crude but effective version of CDC, dont forget that once you have Loaded batch of records you simply update the flag on the trigger table to say they are loaded, not forgetting to clear it down once a day.

Hi Jason,

I hope you are doing fine. Would it be possible for you to create a sample query based on what you have mentioned in your solution ? I think it can be achieved using your suggestion but I am not sure how to make a start with this.

Thanks and Regards,

Paul