Friday, March 23, 2012

Package structure in SSIS

Hi

I have been working with DTC packages together with analysis service, and are

now begining on SSIS.

But I have some demands that's been giving me headache before, so I hope

there's a solution in the SSIS for the following.

My DTC packages was build to be executed i 5 steps/Packages

1. Transfeer data from my Oracle 9.2.0.4 to SQL 2000

2. Transform data

3. Quality check

4. Process dimension

5. Process cubes

BUT in 2005, I want a little more flexibilty. I want to execute smaller

packages, let's say, only my "general ledger".

This way I will be able to update some of my important cubes quicker. And

not wait until all data

is transferred.

So what I have done until now is that I have created SSIS packages per module. I

have a General_ledger.dtsx and a Sales.dtsx. Which I can execute separately.

I also have a MasterFlow.dtsx which contain an "Execute package Task"

for each package.

I can run the MasterFlow and it will run through all packages once every night.

Each package contains

1. Truncate package tabels

2. Transfer package

tabels

3. Execute dimensions

data

4. Execute cubes

5. Process

Dimensions

6. Process Cubes

Is this the best way?

I'm not satisfied

myself because,

when I run MasterFlow. I would like to execute all

"step 1" in all packages, before "step 2" and so forth.

Then I would be sure that all data is OK when all Cubes are processed, if I

have a virtual cube that contains data from 2 packages.

How would you design this kind of setup?

Thanx in adv.

Best Regards

Bjarne R Mikkelsen

A-TEX A/S

Why not have a third child package that processes the cubes? then you only have to execute the cube process upon successful completion of everything else.

Or, in each child package you could process the measure group (I'm assuming you are using AS2005) relevant to teh data that you have just transformed.

Your approach up to now seems pretty sound to me.

-Jamie

|||Thank you.

The processing of dimension and cubes are the least time consuming, so it would be a possibility, simply to arrange these in a 3. child package, which will be executed everythime a single package is run.

/Bjarne

No comments:

Post a Comment