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!