HOW run ssis package on table update

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » HOW run ssis package on table update

HOW run ssis package on table update

Rate Topic

Display Mode

Topic Options

Author

Message

mpradeep23

Posted Wednesday, January 02, 2013 10:13 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 47, Visits: 173

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

SQLRNNR

Posted Wednesday, January 02, 2013 10:32 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

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 2008

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

mpradeep23

Posted Wednesday, January 02, 2013 10:43 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 47, Visits: 173

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

SQLRNNR

Posted Wednesday, January 02, 2013 10:47 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

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.

Post #1402183

Evil Kraig F

Posted Thursday, January 03, 2013 1:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

mpradeep23 (1/2/2013)

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

pmm.sudarsan

Posted Tuesday, January 08, 2013 6:47 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 6:46 PM
Points: 16, Visits: 36

For this scenario we can create a trigger to flow the process instead of using SSIS package.

Post #1404516

deepak kelath

Posted Wednesday, January 09, 2013 5:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:26 AM
Points: 9, Visits: 148

Dml triggers are good choice in above scenario.

Post #1404708

« Prev Topic | Next Topic »

Permissions