January 2, 2013 at 10:13 pm
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
January 2, 2013 at 10:32 pm
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[/url]
Learn Extended Events
January 2, 2013 at 10:43 pm
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
January 2, 2013 at 10:47 pm
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[/url]
Learn Extended Events
January 3, 2013 at 1:32 pm
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
January 8, 2013 at 6:47 pm
For this scenario we can create a trigger to flow the process instead of using SSIS package.
January 9, 2013 at 5:23 am
Dml triggers are good choice in above scenario.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply