Post reply

SSIS OLEDB Destination Options

  • January 18, 2012 at 10:15 pm

    #391349

    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

  • January 18, 2012 at 10:18 pm

    #1435646

    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:

  • January 19, 2012 at 12:04 am

    #1435659

    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

  • January 19, 2012 at 12:23 am

    #1435669

    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

  • January 19, 2012 at 2:10 am

    #1435699

    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?

  • January 19, 2012 at 5:16 am

    #1435742

    Took a shot, failed.

    At least I learned something.

    Thanks for the question.

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

  • January 19, 2012 at 5:28 am

    This was removed by the editor as SPAM

  • January 19, 2012 at 6:49 am

    #1435794

    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.

  • January 19, 2012 at 7:15 am

    #1435823

    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

  • January 19, 2012 at 7:38 am

    #1435844

    Good question.

  • January 19, 2012 at 7:38 am

    #1435845

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

    Thanks,

    Steve

  • January 19, 2012 at 7:42 am

    #1435853

    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.

  • January 19, 2012 at 8:04 am

    #1435887

    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

  • January 19, 2012 at 9:11 am

    #1435957

    tks for the question

  • January 19, 2012 at 9:44 am

    #1436010

    "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 23 total)

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