sql server - Execute SQL task with T-SQL sp or Executing the Sp from SSMS ,which one is performance wise better? -

i have t-sql stored procedure, inserts millions of records sql server table. have 2 options execute stored procedure

1. execute ssis "execute sql task" sp
2. execute sp in ssms

which 1 execute faster, , provide better performance?

(via comment) sp loads csv file sql 2000 database. planning use ssis 2005 migrate csv sql 2000 table, method better

you invoke stored procedure using .net library, calling command line (osql, sqlcmd, etc), starting in sql agent job calling program doesn't matter in slightest.

ultimately, sql server database engine receive request you'd run stored procedure , same parameters, it's going use same query plan regardless of caller.

if observing differences, check out erland's article: slow in application, fast in ssms

so, real question seems loading csv. stored procedure approach should still give same performance regardless of how it's started. long have fixed csv file (you aren't trying write generic import-anything), might better performance using actual data flow task. it'd easier troubleshoot.

and unasked advice, if you're starting out of sql server 2000, skip 2005 , head sql server 2008 r2. sql server end of life has occurred 2000. 2005 expires in 2016. change in licensing costs shouldn't bad migrating 2000 2008 r2 shouldn't bad. 2012 though, that's going cost pretty penny. pity, it's beauty.