• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

bulk inserts vs ssis inserts

Is a SSIS data transfer always more efficient and faster than a bulk load/insert operation?

thanks
0
anushahanna
Asked:
anushahanna
4 Solutions
 
carsRSTCommented:
BCP would be a little faster, but SSIS has many advantages, such as allowing a start to finish data flow.
0
 
k_murli_krishnaCommented:
There is a bulk insert task in SSIS itself.
Execute SQL and Bulk Insert Tasks in SSIS
http://www.sql-server-performance.com/articles/dba/execute_sql_bulk_insert_tasks_ssis_p1.aspx

An excellent reference:
Quick Table Transfers (Imports) using SSIS, Bulk Insert or BCP
http://sqlserverplanet.com/data-warehouse/quick-table-transfers-imports-using-ssis-bulk-insert-or-bcp/

For additional idea:
BULK INSERT - SSIS vs. Stored Procedures
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/72924/BULK-INSERT-SSIS-vs-Stored-Procedures
0
 
Reza RadConsultant, TrainerCommented:
bulk insert fastest way to load data,
but SSIS has many other advantages,
suppose sometimes which you cannot use bulk insert, sometimes data source and destination are from different engines,
some times you need to change data during transfer( for example pivot them or ....) ,
sometimes you need a control logic on the data transfer operation for example a loop based on something and ....

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
anushahannaAuthor Commented:
so would the foll be accurate:
it is raw transfer (straight column to column transfer) - then BCP or SP is OK. But for transformations/constraints checks etc, then SSIS best?
0
 
Reza RadConsultant, TrainerCommented:
>>so would the foll be accurate:
it is raw transfer (straight column to column transfer) - then BCP or SP is OK. But for transformations/constraints checks etc, then SSIS best?<<
Yes. you got it correctly.
0
 
anushahannaAuthor Commented:
Thanks.
0
 
Reza RadConsultant, TrainerCommented:
Glad to help,
Regards,

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now