SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HOW run ssis package on table update


HOW run ssis package on table update

Author
Message
mpradeep23
mpradeep23
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 408
Hi

i have created ssis package to move data from one table to other in different instances when i update in the source table then ssis package should run simultaneously and update the destination table can any specify how to do this

thanks in advance
regards
pradeep
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68821 Visits: 18570
From what you describe I would say SSIS is the wrong technology to use for your needs.

It appears that you need replication or logshipping and not SSIS.

Can you clarify your requirements?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

mpradeep23
mpradeep23
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 408
i have two database
one is main
other is dev
in the main i have a two tables called users and userinformation
i need only few columns from users and userinforamtion and insert into dev
database table
for that i have created ssis package for insert and update
but when ever in main database users or userinforamtion table updated i need update it to dev database at that particulr time i need to run ssis package

thanks
pradeep
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68821 Visits: 18570
In that case I would most likely replicate those two tables.

But since you insist on using SSIS, you can create an agent job and then put a trigger on those tables that will cause that agent job to run when an update or insert occurs.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21623 Visits: 7660
mpradeep23 (1/2/2013)
i have two database
one is main
other is dev
in the main i have a two tables called users and userinformation
i need only few columns from users and userinforamtion and insert into dev
database table
for that i have created ssis package for insert and update
but when ever in main database users or userinforamtion table updated i need update it to dev database at that particulr time i need to run ssis package

thanks
pradeep


This is flawed, at best. A few questions:
Why are you trying to keep dev in lockstep with production?
Why are you avoiding using replication?
Why SSIS? A linked server would be your better bet even if avoiding replication, though comes with other issues.
What is the size of this table? Rowwidth and rowcount?
Does this table have an auditing table associated with it that you can use to delta moves?

You want triggers, those are what react to data changes in a particular table. You don't want a trigger to be reliant, usually, on anything external to the database. For example, dev SLAs are much lower than production. If your dev server goes down and it hoses up your production database, was that really what you wanted? Is dev consistency that important?

My recommendation: institute transactional replication for the particular table in question, as Jason recommended above as well. Firing off SSIS packages or job steps from triggers is fraught with problems and possibly missed data. You'd be better off creating a package that determines and moves deltas between the systems and loops every 3 minutes or so, or just truncate/reloads the dev database.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
pmm.sudarsan
pmm.sudarsan
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 105
For this scenario we can create a trigger to flow the process instead of using SSIS package.
deepak kelath
deepak kelath
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 213
Dml triggers are good choice in above scenario.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search