Pivot in TSQL Vs SSIS

I have to perform an UNPIVOT operation on a table with 20 million rows. After the Pivot completes, I am going to do financial calculations, aggregations and write to another table which makes it a narrow but 70 million row result.

Where am I better off doing the UNPIVOT? My choices are

TSQL (in the SELECT statement)
Read the wide table into SSIS and do an UNPIVOT in SSIS.

asked Sep 01 '11 at 01:05 PM in Default

Raj More
1.7k ● 74 ● 78 ● 82

(comments are locked)

1 answer:

There are so many factors to consider before making a recommendation, and the best person to do this is you! And, in the end, it's going to be a case of suck it and see. Some things for you to think about when making your decision:

can the box running the SSIS job cope with that size task?
Is there sufficient processing capacity on the SQL Server to handle it directly?
Where's the data going? Same server?
If the SSIS job is running on a separate box, how fast is the network between the two? You're going to have a lot of data flying around...
Is this a one-off job, or a regular task? If regular, which is going to be quicker / more consistent to run / allow for better error handling / scheduling? If one-off, which is going to be quicker / easier to develop and test?