rhythm.varshney (11/10/2012)

---

I have a scenario where an excel have thousands of records with say 20 columns. In which their is a column which I need to split while inserting it into main table and also need to perform other operations as well.
I am using a staging table to import the data from excel and then performing rest of operations on the staging table before inserting the records to main table.
So what should be better approach from performance point of view - derived columns transformation or should I split it in a stored proc which is dealing with other operations as well.

Please advise.

Randy Knight (11/11/2012)

---

So you are importing the column to your staging table in it's native format and then splitting it from there? On what basis are you splitting it (i.e. at a certain column #, cerain character, etc.)? We need some more info to answer but in general I would think SSIS will be better at this kind of operation than T-SQL.

rhythm.varshney (11/12/2012)

---

yes.I am importing the data to staging table as it is and there I am applying split on a column say name into first name and last name on the basis of space between two.
so you meant too say derived column would be better approach ??

Randy Knight (11/12/2012)

---

Yes. The SSIS expression language is going to be much easier to do this in. And while it is a bit of a paradigm shift, get away from the staging table idea. We want to be doing ETL (Extract, Tranform, Load) rather than ELT (Extract, Load, Transform). SSIS excels at doing these things in memory, in the pipeline. Writing to staging table(s) in the interim slows things down considerably. When things get overly complex for a single dataflow, I will often write to raw files for the interim step rather than a db table ... still much faster.

Evil Kraig F (11/12/2012)

---

Right up until you need to sort data for aggregations or the like, I agree with you. When you need to do that, T-SQL tends to blow the dataflow stream away. I agree with the general principal though. In a staging table you need to write at least twice (once in, once transformed), where if you transform before you write, it's a single dropoff.

However, don't completely throw away the idea of a staging table. You cannot UPDATE or DELETE from an existing table from SSIS in bulk. If you're doing a MERGE equivalent and not merely delivering new rows to the existing table, deliver to staging and then MERGE it in. That's still part of the LOAD components of ETL though, so I may just be splitting hairs with what Randy was recommending.

Evil Kraig F (11/12/2012)

---

Right up until you need to sort data for aggregations or the like, I agree with you. When you need to do that, T-SQL tends to blow the dataflow stream away. I agree with the general principal though. In a staging table you need to write at least twice (once in, once transformed), where if you transform before you write, it's a single dropoff.

However, don't completely throw away the idea of a staging table. You cannot UPDATE or DELETE from an existing table from SSIS in bulk. If you're doing a MERGE equivalent and not merely delivering new rows to the existing table, deliver to staging and then MERGE it in. That's still part of the LOAD components of ETL though, so I may just be splitting hairs with what Randy was recommending.