Ayende @ Rahien

Hi!
My name is Ayende Rahien
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: 5,949 | Comments: 44,548

filter by tags archive

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.

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

No future posts left, oh my!

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats