Default sorting order on View in SSIS is different from DTS.

[CJ]

Hi,

I have very poorly designed DW and OLAP environment and am having
problem with different behavior of default sorting order on database
View in SSIS and DTS transformation tasks. The problem is that our
OLAP is dependent on surrogate key whose table is refreshed entirely
every time. The table is static pretty much we did not have any
problem although our Cognos Cube is dependent on the surrogate key as
the surrogate key has been always the same with DTS package. I am
trying to migrate the DTS to SSIS and I found the source data sets
from the same view are different between DTS version and SSIS version
- SSIS appears to be picking up the first column, which happens to be
a numeric key from source system, for the sorting order whereas DTS
does not.

Have any of you expericed this issue? Is there any setting I can set
in SSIS package(or engin) to mimic the DTS sorting order?

Thank you for your help.

CJ

[Todd C]

Two possible things you could do:

In the SQL Statement of the Source Adapter in your data flow, add an ORDER
BY clause.
Or
Add a Sort Transform in the data flow.

Just curious: why would the sort order of the load matter to anything? Seems
a REALLY POOR design indeed if the application that consumes the data
(analysis cubes or others) depends on the physical ordering of records to
make the application work right.

Of course, if you have a CLUSTERED INDEX on the destination table, then it
won't matter at all how you sort the data inside the package as the index
will re-order it during the physical writing.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by CJ
Hi,
I have very poorly designed DW and OLAP environment and am having
problem with different behavior of default sorting order on database
View in SSIS and DTS transformation tasks. The problem is that our
OLAP is dependent on surrogate key whose table is refreshed entirely
every time. The table is static pretty much we did not have any
problem although our Cognos Cube is dependent on the surrogate key as
the surrogate key has been always the same with DTS package. I am
trying to migrate the DTS to SSIS and I found the source data sets
from the same view are different between DTS version and SSIS version
- SSIS appears to be picking up the first column, which happens to be
a numeric key from source system, for the sorting order whereas DTS
does not.
Have any of you expericed this issue? Is there any setting I can set
in SSIS package(or engin) to mimic the DTS sorting order?
Thank you for your help.
CJ

[CJ]

Thanks again, Todd.

Yes, I was inherited this system and it really is poorly designed.
Forcing the order is what I ended up doing. Luckly, I could mimic the
DTS sorting order from SSIS.
But, I was curious why DTS and SSIS behave differently on this because
there are more potential places that would be affected by this.

Thanks.

c.j.