mpradeep23 (1/2/2013)
i have two databaseone 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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA