July 28, 2014 at 6:09 pm
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
July 29, 2014 at 1:00 am
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
July 29, 2014 at 4:48 pm
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.
July 30, 2014 at 12:24 am
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