philcart (2/5/2013)
Sorry you lost me on a few fronts.1) you reference another article stating, "The method SSIS Data Pump represents the most effective way to load large amount of data into a SQL Server database because the SSIS Data Pump makes optimal usage of the transaction logs, parallelism, locking, and pipelining. A more detailed explanation is in the referenced article.". Yet I see no such detailed explanation in the referenced article.
You are right , there is no such detailed explanation.
Purpose of my article was not explaining why SSIS Data Pump is the best method.
You can give a look to :
http://blog.staticvoid.co.nz/2012/8/17/mssql_and_large_insert_statements
and note that SSIS data pump is nothing else than a pipelined bulk copy.
2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.
How could I launch from T-SQL a SSIS package ? The only way I know is using xp_cmdshell.. purpose of the article was a T-SQL stored procedure able to launch a SSIS package, and this requires xp_cmdshell
3) your xp_cmdshell calls are executing a bunch of commands you can do in SSIS with the FileSystem task, or at worst the Script Task.
Yes, some of xp_cmdshell calls could be moved into BULK_TRANSFER_DATA.dtsx