Is SSIS packages are best solution for importing and exporting the large amount of data?

Question

my requirement is that in nop commerce 1.9 i have to insert multiple discount form a excel sheet which have lot of data so before doing this task i need to be clear in mind which one is best solution for this.

Which is the fastest way to upload the excelsheet having more than 100,000 lines of code in C#?

i read this question and answer found that SSIS is an option .

is really SSIS is best for large size file import and export options. and what other benefits i will get if i use SSIS packages ?

Answer 1

For ~100,000 rows, performance should not be a significant problem with this type of data. SSIS can do this, but it is not the only option. I think there are three reasonable approaches to doing this:

SSIS: This can read excel files. If your spreadsheet is well behaved (i.e. can be trusted to be laid out correctly) then SSIS can load the contents. It has some error logging features, but in practice it can only usefully dump a log file or write errors out to a log table. Erroneous rows can be directed to a holding table.

Pros

• Load process is fairly easy to develop.
• SSIS package can be changed independently of the application if the spreadsheet format has to change.
• Can read directly from spreadsheet file

Cons:

• Dependency on having SSIS runtime installed on the system.
• SSIS is really intended to be a server-side installation; error handling tends to consist of writing messages to logs. You would need to find a way to make error logs available to the user to troubleshoot errors.

BCP or BULK INSERT: You can export the spreadsheet to a CSV and use BCP or a BULK INSERT statement to load the file. However, this requires the file to be exported to a CSV and copied to a drive on the database server or a share accessible to it.

Pros:

• Fast
• bcp can be assumed to be present on the server.

Cons:

• Requires manual steps to export to CSV
• The file must be placed on a volume that can be mounted on the server
• Limited error handling facilities.

SqlBulkCopy API: If you're already using .Net you can read from the spreadsheet using OLE automation or ODBC and load the data using the SQL Server Bulk Load API. This requires you to write a C# routine to do the import. If the spreadsheet is loaded manually then it can be loaded from the user's PC.

Pros

• Does not require SSIS to be installed on the computer,
• file can be located on user's PC
• Load process can be interactive, presenting errors to the user and allowing them to correct the errors with multiple retries.

Cons:

• Most effort to develop.
• Really only practical as a feature on an application.