Ayende @ Rahien

Refunds available at head office

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
07/19/2007 12:16 AM by
Jon Limjap

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

Ayende Rahien
07/19/2007 03:31 AM by
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
07/19/2007 07:32 AM by
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
07/19/2007 09:57 AM by
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
07/19/2007 08:39 PM by
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
07/19/2007 08:39 PM by
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
07/19/2007 08:44 PM by
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
07/23/2007 02:58 PM by
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
07/24/2007 05:34 PM by
Cassio Tavares

What tool did you used to draw this flow?

Thank you

Comments have been closed on this topic.