Common encountered errors in SSIS

  • Hi,

    I am currently writing my Bachelor Thesis about error handling in SSIS and generally increasing the robustness of ETL processes.

    One of the problems I am facing right now is that I have only limited access to developer experience on errors regularly encountered in SSIS packages.

    This is the reason why I would like to ask every interested reader to give some examples of errors they have encountered in SSIS (during runtime) so far. Issues I have encountered so far and will cover in my thesis include missing references in source systems, unanncounced changes to source system data models and connection failure during nightly ETL-runtime.

    I am very thankful for every example and will try to incorporate them with a solution prototype in my thesis.

    Best Regards,

    Vel

  • Common errors:

    * data type mismatch. For example date is expected but a regular string pops up.

    * truncation. For example text is too long.

    * code page mismatch. You have the regular latin code page (1252) and suddenly you get those weird German symbols in an address.

    * connection failures (you already mentioned this + timeout issues)

    * metadata changes (source and/or destination. Can happen unexpected with Excel files)

    * primary key/unique index violation (duplicate values)

    * bad input file format (for example, no text qualifier is chosen and suddenly the delimiter appears inside the data. Or CR/LF becomes LF as row delimiter or the header is repeated multiple times in the data)

    * permission issues

    * configuration file is missing and suddenly you are inserting data in the test environment instead of the production environment

    * out of memory issue

    * database/log file too big (disk is full and SQL Server stops inserting data)

    * deadlocks

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thanks for those!

    Can someone give me an example scenario with an occurring deadlock? I have not encountered this so far - so I'm not sure on how to tackle this.

  • Velym (7/29/2014)


    Thanks for those!

    Can someone give me an example scenario with an occurring deadlock? I have not encountered this so far - so I'm not sure on how to tackle this.

    Typically this can encounter when you are using stored procedures and/or execute SQL tasks with transactions. So the typical deadlock scenarios you can find in literature, but just executed from SSIS.

    I remember having SSIS lock up once because I used multiple data flows with transactions[/url] that read and write to the same table simultaneously.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

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