+972 52-548-6969
Why I hate SSIS: Part N+1
Take a look at this (highly simplified) package. The flow is very simple, truncate shadow tables, copy to shadow tables, perform cleanup and copy to the real tables. The inside of each data flow is simple Source -> Convert to Unicode -> Destination. Not really complicated or ground breaking, right?
The shadow tables have the same structure as the real tables, but they have no FKs.
It failed on me, in production, but only when running as a job! The error? Primary Key violation in the Products Statuses and Order Statuses tables. I have verified that:
- I am truncating those tables
- That only one instance of the job is running at any single time
- That the source data is neither changing nor invalid (those are lookup tables with 6 and 2 values, respectively)
99% reproducible, it would give an error when running as a job, 50% reproducible, it would give the error when running from dtexec.
After hitting the wall with it for way too long, I "fixed" it by doing:
Why it would make a difference, I have no idea.
The original version is what I was using for the last four or five months, and it never gave me any problems, until three days ago, just when I wanted to load it to production.
I have began thinking about how to build my own "ETL engine" for the next project, code is reliable, safe, debuggable and easy to understand. Something in Boo is probably the way to go...
I had enough of fighting with tools!
Comments
Maybe someone put up a constraint somewhere involving both tables? Not necessarily in those tables themselves?
No, those are literally shadow tables, created for the sole purpose of the ETL process.
No constraints, no FK and nothing can refer to them. The error is PK violation error, as well.
Oren,
the sequence container you where having is executing the components concurrently depending on the number of CPUs.
most probably, the status tables were not copied in the right order.
your solution was to correctly serialize.
@Muhammad,
There is no dependencies between the tasks, and they are intended to run as parallel. Having to run them serially is a bug.
You say that you get Primary Key violations and you don't have them on your shadow tables, then they are propable generated by the source tables. When you are copying the tables: how do you do that? Maybe your are locking the source tables (a select can lock them), while other people are changing the data in the source tables.
You say that you get Primary Key violations and you don't have them on your shadow tables, then they are propable generated by the source tables. When you are copying the tables: how do you do that? Maybe your are locking the source tables (a select can lock them), while other people are changing the data in the source tables.
I have a PK constraint defined on the shadow tables, and I get those values from the _look up tables_, which no one has modified in the last three years.
Sometimes building not buying is exactly why you do it. You need to know it works, that you can always fix it / tune it up, and keep the level of abstraction low so you can actually debug processes.
What tool did you used to draw this flow?
Thank you
SSIS itself
Comment preview