Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 18 | Comments: 72

filter by tags archive

Why I hate SSIS: Part N+1

time to read 2 min | 277 words

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.

image

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:

image

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

Jon Limjap

Maybe someone put up a constraint somewhere involving both tables? Not necessarily in those tables themselves?

Ayende Rahien

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.

Muhammad Z

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.

Ayende Rahien

@Muhammad,

There is no dependencies between the tasks, and they are intended to run as parallel. Having to run them serially is a bug.

Remy

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.

Remy

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.

Ayende Rahien

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.

Mr_Build

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.

Cassio Tavares

What tool did you used to draw this flow?

Thank you

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. RavenDB 3.0 New Stable Release - 5 hours from now
  2. Production postmortem: The industry at large - about one day from now
  3. The insidious cost of allocations - 2 days from now
  4. Buffer allocation strategies: A possible solution - 5 days from now
  5. Buffer allocation strategies: Explaining the solution - 6 days from now

And 3 more posts are pending...

There are posts all the way to Sep 11, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    01 Sep 2015 - The case of the lying configuration file
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats