SSIS Data Flow Task or Direct Insert in SP - Which is faster

• GonnaCatchIT

  SSCrazy

  Points: 2797

  More actions

  February 13, 2016 at 12:12 am

  #324324

  Dear All,

  I need to insert about 3 lakh records after processing the data from different tables into another destination table.

  I am doing all the logics, joins in an SP and it returns those 3L records to SSIS package and then using Dataflow task to insert those records using sql destination.

  My Question: Is data flow task for Insert faster or If I insert those 3 L records directly into the table in my SP itself instead of returning it back to SSIS?

  Any thoughts would be helpful.

• GonnaCatchIT

  SSCrazy

  Points: 2797

  More actions

  February 15, 2016 at 5:37 am

  #1858751

  Any pointers or feedback ?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  February 15, 2016 at 5:47 am

  #1858753

  If the source and destination are in the same database, or even a different database on the same instance, I'd be surprised if SSIS were the better option. But why don't you try both for yourself?

  John