Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

HOW run ssis package on table update Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 10:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 69, Visits: 306
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
Post #1402172
Posted Wednesday, January 2, 2013 10:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:58 PM
Points: 21,755, Visits: 15,461
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1402178
Posted Wednesday, January 2, 2013 10:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 69, Visits: 306
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
Post #1402182
Posted Wednesday, January 2, 2013 10:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:58 PM
Points: 21,755, Visits: 15,461
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1402183
Posted Thursday, January 3, 2013 1:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 6,259, Visits: 7,453
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
Post #1402531
Posted Tuesday, January 8, 2013 6:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:57 AM
Points: 16, Visits: 50
For this scenario we can create a trigger to flow the process instead of using SSIS package.
Post #1404516
Posted Wednesday, January 9, 2013 5:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 15, 2014 6:57 AM
Points: 14, Visits: 193
Dml triggers are good choice in above scenario.
Post #1404708
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse