SSIS OLEDB Destination Options

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Comments posted to this topic are about the item SSIS OLEDB Destination Options


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • tofan.nayak

    SSC Enthusiast

    Points: 102

    Trigger always fire when SSIS load or insert in SQl Server Database label or table base trigger

    more.....

    http://blog.sqlauthority.com/2009/05/27/sql-server-interesting-observation-of-logon-trigger-on-all-servers/

    S/W Tofan Nayak:hehe:

  • SQLRNNR

    SSC Guru

    Points: 281252

    I learned something new today - again.

    Yay.

    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

  • Evil Kraig F

    SSC Guru

    Points: 100851

    tofan.nayak (1/18/2012)


    Trigger always fire when SSIS load or insert in SQl Server Database label or table base trigger

    more.....

    http://blog.sqlauthority.com/2009/05/27/sql-server-interesting-observation-of-logon-trigger-on-all-servers/%5B/quote%5D

    That's for logon triggers. Setup a table trigger and try for yourself with an insert trigger.

    CREATE TABLE SourceData

    (SomeData VARCHAR(20))

    INSERT INTO SourceData VALUES ('abc')

    INSERT INTO SourceData VALUES ('def')

    INSERT INTO SourceData VALUES ('ghi')

    INSERT INTO SourceData VALUES ('jkl')

    CREATE TABLE TargetData

    (tdID INT Identity(1,1) NOT NULL,

    SomeData VARCHAR(20))

    CREATE TABLE DataLog

    (tdID INT, DateOfEntry DATETIME DEFAULT (GETDATE()))

    CREATE TRIGGER tr_i_TargetData

    ON TargetData

    FOR INSERT

    AS

    INSERT INTO DataLog (tdID)

    SELECT tdID FROM Inserted

    GO

    INSERT INTO TargetData (SomeDAta) VALUES ('TestEntry')

    SELECT * FROM DataLog

    Now do a datapump in SSIS 2k5 from source to target and then recheck the DataLog table. The logging will not have fired.


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • cengland0

    SSCertifiable

    Points: 6102

    Evil Kraig F (1/19/2012)


    Now do a datapump in SSIS 2k5 from source to target and then recheck the DataLog table. The logging will not have fired.

    But it will if you use the FIRE_TRIGGERS option in the FastLoadOptions property in the Advanced Editor dialog box, right?

  • SqlMel

    SSCrazy

    Points: 2891

    Took a shot, failed.

    At least I learned something.

    Thanks for the question.

    ---------------
    Mel. 😎

  • This was removed by the editor as SPAM

  • Mike Palecek

    SSCommitted

    Points: 1560

    Nice question, and good to know.

    It seems many people are guessing at this one, The answers were split 50/50 when I saw it.

  • SQLRNNR

    SSC Guru

    Points: 281252

    cengland0 (1/19/2012)


    Evil Kraig F (1/19/2012)


    Now do a datapump in SSIS 2k5 from source to target and then recheck the DataLog table. The logging will not have fired.

    But it will if you use the FIRE_TRIGGERS option in the FastLoadOptions property in the Advanced Editor dialog box, right?

    Yes

    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

  • JAZZ Master

    Hall of Fame

    Points: 3292

    Good question.

  • Tock

    Default port

    Points: 1432

    Good question; it's always nice to learn something new about a frequently used tool.

    Thanks,

    Steve

  • jeff.mason

    SSCrazy Eights

    Points: 9555

    tofan.nayak (1/18/2012)


    Trigger always fire when SSIS load or insert in SQl Server Database label or table base trigger

    more.....

    http://blog.sqlauthority.com/2009/05/27/sql-server-interesting-observation-of-logon-trigger-on-all-servers/%5B/quote%5D

    Not if you are doing bulk load. If you are doing bulk load, the default is to disable triggers. You can certainly turn them on, but you cannot say that they always fire.

  • Jack Corbett

    SSC Guru

    Points: 184381

    Great question. You either have to look it up or have used the setting to know the answer. I got it wrong because I've never been in the advanced settings there.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3994

    tks for the question

  • Scott McCandless-392284

    SSC Enthusiast

    Points: 163

    "Specifies whether triggers fire on the insert table. The option has the form FIRE_TRIGGERS. The presence of the option indicates that triggers fire."

    -http://msdn.microsoft.com/en-us/library/ms141237.aspx

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply