question

As discussed in the comments, here is the solution in C#, which may be split into SSIS package steps if needs to be, but this would be an overkill as SSIS adds quite a bit of weight, so creating the SQL Job with the single step to call the EXE could do it instead. Optionally, this C# solution may be easily restated into T-SQL script. The pure T-SQL solution is listed below the C# solution. C# code: static void Main(string[] args) { try { using (StreamReader reader = new StreamReader("c:\\temp\\source.csv")) { // Script to drop the staging table if it already exists from the previous import. // This might be necessary in case if each source file has different shape/dlimiter. string dropTable = @" if exists ( select 1 from sys.objects where [object_id] = object_id(N'dbo.StagingTable') and [type] = N'U' ) drop table dbo.StagingTable;"; // Script which may be used to create staging table before bulk inserting data into it. string createTable = "create table dbo.StagingTable("; string headers = reader.ReadLine(); // the line below assumes that the delimiter is pipe or comma. Enhance if necessary. string delimiter = headers.Contains(",") ? "," : "|"; string bulkInsert = "bulk insert dbo.StagingTable from 'c:\\temp\\source.csv' " + "with (fieldterminator = '" + delimiter + "', firstrow = 2);"; List columns = new List(headers.Split(delimiter.ToCharArray())); columns = columns.ConvertAll(item => item += " varchar(100) null"); createTable += string.Join(", ", columns) + ");"; reader.Close(); using (SqlConnection cn = new SqlConnection("Integrated Security=true;Initial Catalog=Test;server=.")) { cn.Open(); using (SqlCommand cm = new SqlCommand()) { cm.CommandType = CommandType.Text; cm.Connection = cn; // drop table if exists cm.CommandText = dropTable; cm.ExecuteNonQuery(); // create staging table cm.CommandText = createTable; cm.ExecuteNonQuery(); // bulk insert records from delimited file cm.CommandText = bulkInsert; cm.ExecuteNonQuery(); } // instance of command object is disposed // Close the connection cn.Close(); } // connection is disposed } // reader is disposed } catch (Exception ex) { Console.WriteLine("Error occurred: " + ex.Message); // throw ex; /* re-throw error if needed */ } // Console.ReadLine(); } When I tested this solution on my old Windows 7 laptop with 8 GB of RAM, I saw that importing a million rows takes about 10 seconds, meaning that I had about 100K worth of records per second import speed. This is still better that pumping data from flat file via SSIS package. The import speed should be better if the SQL Server instance is more powerful than my old laptop :) The same can be accomplished with T-SQL. I will add pure T-SQL solution to this answer in about 30 minutes (or whatever it will take me to "translate" the C# to T-SQL. Even though ordinarily C# is about **what you can do to a row** while T-SQL is about **what you can do to a column**, it should be a straightforward exercise in this case because there was no any "row processing" in the C# in this answer. *** Edit (added T-SQL solution, and also handling of the double quotes as text qualifier) *** The T-SQL script which accomplishes the same thing, and also handles the double quotes as text qualifier (which will work if everything is qualified, header and lines). Usually, there is a reason why the text qualifier is needed and used, it is there to handle the possible scenario when the column's value itself includes the delimiter as a part of it's value. In this case, the common convention is to qualify all fields and all values in the file with double quotes. Using the separate process to "prepare" the file by removing all double quotes is, therefore, ill advised. For example, suppose there is a field named **FullName**, and the value of this column in one of the records is **Bozo, The Clown**. If the file is comma-delimited then it would cause problems with bulk insert which has comma as the field terminator, unless the file is text qualified with double quotes. One of the ways to handle this situation is to specify the field terminator as **","**. This will allow to still use bulk insert dynamically without the need of format file. The only downside is in this case the first column and last column will end up prefixed or suffixed with double quote respectively. This will necessitate the final touch up (the update of all rows of the staging table, replacing the double quote in first and last columns with blank). The good news is that this final touch up will be executed only if necessary. Here is the updated script: declare @headers varchar(max); declare @delimiter varchar(3); /* char(1) is not enough if text qualifier is present */ declare @createTable nvarchar(max); declare @rowTerminator varchar(6); declare @bulkInsert nvarchar(max); declare @lastColumn int; declare @updateScript nvarchar(max); -- read the header row, figure out delimiter and column names, and generate bulk insert script select @headers = replace(substring(t.BulkColumn, 1, charindex(char(10), t.BulkColumn) - 1), char(13), ''), @delimiter = case when charindex('","', @headers) > 0 then '","' when charindex('"|"', @headers) > 0 then '"|"' when charindex(',', @headers) > 0 then ',' else '|' end, -- outer replace is needed to remove 2 double quotes (in the beginning and the end of the headers) @createTable = replace('create table dbo.StagingTable(' + replace(@headers, @delimiter, ' varchar(50) null,') + ' varchar(50) null)', '"', ''), @rowTerminator = case when charindex(char(13) + char(10), t.BulkColumn) > 0 then '0x0d0a' else '0x0a' end, @bulkInsert = 'bulk insert dbo.StagingTable from ''c:\temp\4357-test2.txt'' with ( fieldterminator = ''' + @delimiter + ''', firstrow = 2, rowterminator = ''' + @rowterminator + ''' );' from openrowset(bulk 'c:\temp\4357-test2.txt', single_clob) t; -- drop the staging table if already exists if exists( select 1 from sys.objects where [object_id] = object_id(N'dbo.StagingTable') and [type] = N'U' ) drop table dbo.StagingTable; -- create staging table exec sp_executesql @statement = @createTable; -- bulk insert records from delimited file exec sp_executesql @statement = @bulkInsert; -- Final touch up, which is needed only if the file included the double quotes as the -- text qualifier: if text is qualified then bulk insert worked but first column ended -- up prefixed and the lastcolumn - suffixed with the double quotes. Remove both. if charindex('"', @delimiter) > 0 begin select @lastColumn = max(column_id) from sys.columns where [object_id] = object_id(N'dbo.StagingTable'); select @updateScript = 'update dbo.StagingTable set ' + stuff(( select ', ' + quotename([name]) + ' = replace(' + quotename([name]) + ', ''"'', '''')' from sys.columns where [object_id] = object_id(N'dbo.StagingTable') and column_id in (1, @lastColumn) for xml path('')), 1, 2, ''); exec sp_executesql @statement = @updateScript; end; go Test of the T-SQL script on my laptop had shown a considerable improvement in import rate as it took only 3 seconds to read the headers, create table and bulk insert a million rows into the staging table, which means that the insert speed was about 300K rows per second. Testing 10 million rows took 40 seconds. Hope this helps. Oleg