+1  A: 

Given your description of your problem: "I want to import an Excel file to an SQL server row-by-row whilst making changes on the data to be imported" - SSIS is the perfect tool for the job.

Importing Excel data with SQL Server Integration Services SSIS with unicode and non-unicode data issues

Mitch Wheat
I edited the post and commented on your previous comment to clarify. It is not as simple as importing some Excel file into SQL Server as other processes are started as well during the import. My question is more about understanding the differences between the different methods
moontear
SSIS is not a reliable way to exchange data between SQL and Excel. Too many bugs.
AlexKuznetsov
+4  A: 

You mention not wanting to use SSIS - but have you considered SqlBulkCopy? Then there is no need for anything except .NET yet you can still use the fastest/most-direct import.

This will accept a DataTable, so you can prepare your data in a DataTable and then pull the trigger. Transactions are optionally supported IIRC. For larger data you can also implement IDataReader to provide fully streaming upload (while still processing each row in transit).

Marc Gravell
`SqlBulkCopy` sounds interesting I will take a look at it
moontear
+1  A: 

The dataSet will probably ending up sending the INSERT statements to the server, so in my opinion, is better to to just send the INSERT statements without the DataSet. You also can have more control over the process, like checking for individual rows for errors, logging, etc.

Eduardo Molteni
A: 

You could convert your processed data table into XML and pass that to a stored procedure in Sql server (in one query) and have your stored procedure parse the XML to create the records.

Chris
Yes I could do that. But why would I? Why not use DataSets?
moontear
A: 
INSERT INTO [dbo].[TableName]
           ([ColumnName1]
           ,[ColumnName2])
)
SELECT [ColumnName1]
           ,[ColumnName2]

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source= PathToFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]
Grant
If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it!
marc_s
my bad thanks for the input
Grant