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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy