CODEWITHSUNDEEP
t-sqlssis
sagar
sagar

Reputation: 159

help needed on SSIS package concept

When data is copied over from source to destination in a SSIS package, source being a sql query with 'group by' keywords used and destination being a table, is it necessary that the data at a row position has to match the data at the same row position at the destination table??

sagar

Upvotes: 0

Views: 361

Answers (2)

Peter Radocchia
Peter Radocchia

Reputation: 11007

Its a performance question, really. Tables have no logical ordering. Or course the data does have a physical order on disk, and I/O has a significant effect on performance, so the best approach will depend on a) how the table is being populated (complete refresh vs. incremental update) and b) how the table is used downstream.

You could create a clustered index on the target table with the same columns as you have in the GROUP BY clause. This will physically order the data on disk by the keys of the clustered index.

If the target table is completely repopulated each time the package is run (drop-recreate or truncate), this may be a good design, since the incoming data will probably be in the right order.

If the target table is incrementally updated each time the package is run, this may be a bad design, since the database will have to interleave the incoming data with existing data on each insert, which can be quite expensive.

Upvotes: 1

DaveE
DaveE

Reputation: 3637

You can use a clustered index to force things to be stored in an ordered way, but as Peter notes this has a performance penalty for incremental updates.

Are you concerened about getting things out in order? That's an ORDER BY on your queries or perhaps you should create a standardised view that shows things in the order you want.

Upvotes: 1

Related Questions