February 23, 2011 at 5:29 am
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
February 23, 2011 at 5:36 am
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
February 23, 2011 at 5:47 am
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
February 23, 2011 at 6:53 am
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
February 23, 2011 at 7:36 am
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
February 23, 2011 at 7:50 am
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
February 23, 2011 at 8:05 am
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
February 23, 2011 at 12:11 pm
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
February 25, 2011 at 2:34 am
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
February 25, 2011 at 2:37 am
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
February 25, 2011 at 2:52 am
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
February 25, 2011 at 2:59 am
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
February 25, 2011 at 3:16 am
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
February 25, 2011 at 3:44 am
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
February 25, 2011 at 3:58 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy