Hi,

We have a legacy application that is dumping large volumes of data to tab delimited files. Each file contains a single record type and all fields are fixed length.

These files can readily be imported into corresponding tables in our SQL server database using the BCP utility from the command line.
We have a VB.Net program written in VS 2003 that imports these files using the SQLDMO.BulkCopy routine.

We are updating the system to use VS 2010 with SQL Server 2008 and according to the Microsoft documentation SQLDMO is no longer available.

I have searched on the internet and have rewritten the import routine to import the tab delimited files into a DataTable using the Microsoft.Jet.OLEDB.4.0 provider. The SqlClient.BulkCopy object is then used to import this DataTable. This issue I am having is that fields in the tab delimited file that are set to spaces are being treating as NULLs when imported into the DataTable. When the DataTable is processed by the SqlClient.BulkCopy the copy fails because the null values are rejected by the SQL table fields that are defined as NOT NULL.

Code used is shown below

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim data As DataTable = RetrieveSourceData()
        CopyData(data)
End Sub

Private Function RetrieveSourceData() As DataTable
        Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\temp\;Extended Properties='text;HDR=No;FMT=TabDelimited'"
        Dim sourcedata As New DataTable
        Using conn As New OleDb.OleDbConnection(connstring)
            conn.Open()
            Dim command As New OleDb.OleDbCommand("Select * from X1.CSV", conn)
            Dim adapter As New OleDb.OleDbDataAdapter(command)
            adapter.Fill(sourcedata)
            conn.Close()
        End Using
        Return sourcedata
End Function

Private Sub CopyData(SourceData As DataTable)
         Dim dbConnString As String = "Data Source=(local);Initial Catalog=XtractDB;User ID=xxxx;Password=yyyy;"
        Using bcp As New SqlClient.SqlBulkCopy(dbConnString)
            bcp.DestinationTableName = "X1"
            bcp.BatchSize = 1000
            bcp.WriteToServer(SourceData)
        End Using
End Sub

In order for the input file to be recognised as TabDelimited I have had to create a schema.ini file in the same directory as the input file. The contents are shown below

[X1.CSV]
Format=TabDelimited

Is there any way I can force the fields with spaces not to be treated as NULL when creating the DataTable?

Is this the best approach for processing the Bulk Copy?

TIA,

Andrew Harper