CODEWITHSUNDEEP
vb.netsqlbulkcopy
Andy
Andy

Reputation: 11

Importing data using SQLBulkCopy

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 being tested 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 via a VB.Net program?

TIA,

Andy

Save to: Default

Switch color theme
Select message background color...
Select message area width...
Adjust message text font size...
Disable auto links Enable acronyms Disable message header Enable auto quote Update the title of this thread... SQL Bulk Copy Thread #1544244 Message #1544244

Upvotes: 1

Views: 10459

Answers (1)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

SqlDMO is deprecated in Sql server 2012 but it's available till Sqlserver 2008 R2 version. SqlBulkCopy is available in all .net frameworks till 4.5 except 1.1.

Refer this link for verification

You can use BULK INSERT (command line utility) to insert multiple records at once.

BULK
INSERT Tablename
FROM 'c:\csvtest.txt'
WITH
(
    FIELDTERMINATOR = ','
    ,ROWTERMINATOR = '\n'
    --,FIRSTROW = 2
    --,MAXERRORS = 0
)
GO

Upvotes: 0

Related Questions