Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Common encountered errors in SSIS Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 6:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:12 AM
Points: 2, Visits: 42
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
Post #1597128
Posted Tuesday, July 29, 2014 1:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1597165
Posted Tuesday, July 29, 2014 4:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:12 AM
Points: 2, Visits: 42
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.
Post #1597524
Posted Wednesday, July 30, 2014 12:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
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 that read and write to the same table simultaneously.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1597584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse