Fire Triggers option not available in SSIS package

  • Hi, I am using a SSIS package to bulk import information from a text file to a SQL Table. The information is very simple and there are no transformations in the middle. The table I use as destination has triggers that I need to be fired when the import process occurs. I know that there is a "Fire Triggers" option in the properties of the package and I used it once in the past but today the check option is simply not there. I mean, it disappeared.

    Any help in order to get back that option would be much appreciated.

  • That option is in the "SQL Server Destination" component. Are you using an "OLEDB Destination" component by accident?

  • Thanks Michael for your answer. Yes, I am using an OLEDB Destination component. The last time I saw the missing option was there but now is gone. I am including a screen capture of my destination component as an attachment.

    Xavier

  • Michael gave you the answer. Change your destination component to be SQL Server, not OLEDB, and the option is there.

    Phil

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Ups, my fault. You are right, the option is there if I use the SQL Server Destination. Thanks a lot for your time

    Xavier:)

  • Hi, I have included FIRE_TRIGGERS in fast load mode and my package is failing with error "An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back"

    Package is succeeded if FIRE_TRIGGERS is removed.

    I have tried inserting one record manually and the trigger functionality is working fine.

    Can someone shed light if there is any other setting that needs to be updated in order to fire triggers using OLE DB destination component. Much appreciated.

  • Sumathi wrote:

    Hi, I have included FIRE_TRIGGERS in fast load mode and my package is failing with error "An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back"

    Package is succeeded if FIRE_TRIGGERS is removed.

    I have tried inserting one record manually and the trigger functionality is working fine.

    Can someone shed light if there is any other setting that needs to be updated in order to fire triggers using OLE DB destination component. Much appreciated.

    Does the trigger handle set-based multiple insertions? Please post the trigger code if you are unsure.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 7 posts - 1 through 7 (of 7 total)

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