title: How to stream data from dataReader to SQL using sqlbulkcopy?
body: <p>I have a requirement to create a desktop application to copy data from an oracle database to a SQL server database (the client explicitly wants this - ETL software, DTS, etc. are all out - it has to be the desktop *.exe)</p>
  
  <p>As the data runs into the millions (often tens or hundreds of millions) of rows, I cannot use sqlbulkcopy(dataTable) as the size of the dataTable very quickly uses all available memory.</p>
  
  <p>I'm using sqlbulkCopy(dataReader) but it is still consuming local memory.</p>
  
  <p>How can I STREAM data from the reader to the sql table, without using massive amounts of local memory?</p>
  
  <p>(skipping the try-catches, etc, just core code):</p>
  
  <pre><code>Dim dr As OleDb.OleDbDataReader
  Dim connString As String = "Provider=MSDAORA.1;User ID=X;password=X;Data Source=X;Persist Security Info=False"
  Dim cnn As OleDb.OleDbConnection = New OleDb.OleDbConnection(connString)
  Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM Table", cnn)
  
  cnn.open
  dr = cmd.ExecuteReader
  
  Dim sqlCnn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Server=X;Database=X;Trusted_Connection=True;Connection Timeout=60")
  sqlCnn.Open()
  Dim bk As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(sqlCnn)
  bk.DestinationTableName = "TABLE_NAME"
  bk.EnableStreaming = True
  bk.BatchSize = 100
  bk.BulkCopyTimeout = 0
  
  bk.WriteToServer(dr)  
  </code></pre>
  
  <p>Throws out of memory exception on local machine, regardless of batch size.  Page file usage rises before dying.
  Expected the datareader to stream to sql server, hence not use much local memory</p>