Migrating from SQL DMO to SQL SMO (SQL Server 2012)

Question

I am migrating the SQL DMO logic to a SQL SMO logic and I am not sure how to achieve the same functionality for a few of the attributes. This is the DMO:

    oBCP = New SQLDMO.BulkCopy2
    With oBCP
        .ImportRowsPerBatch = ImportRowsPerBatch                 
        .DataFilePath = Path.Combine(gtSysTempDir, "file.dat")     
        .LogFilePath = Path.Combine(gtSysTempDir, "file.log")      
        .ErrorFilePath = Path.Combine(gtSysTempDir, "file.err")     
        .MaximumErrorsBeforeAbort = 1                   
        .DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_TabDelimitedChar
    End With

This is the SMO so far:

    trans = New Transfer
    With trans
        .ImportRowsPerBatch = ImportRowsPerBatch                 
        .TargetDatabaseFilePath = Path.Combine(gtSysTempDir, "file.dat")     
        .TargetLogFilePath = Path.Combine(gtSysTempDir, "file.log")       
        '.ErrorFilePath = Path.Combine(gtSysTempDir, "file.err")     
        .MaximumErrorsBeforeAbort = 1                   
        .DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_TabDelimitedChar
    End With

What are the equivalents, if they exist, of ImportRowsPerBatch (BatchSize?), ErrorFilePath, MaxErrorsBeforeAbort, and DataFileType? Thanks.

Answer 1

Despite what the MSDN says, Transfer is not actually the equivalent of BulkCopy2; it's intended for scripting/copying entire databases whereas BulkCopy2 is only for bulk copying data. The settings don't correspond at all -- in particular, Transfer.TargetDatabaseFilePath is supposed to be the location of a database file, whereas BulkCopy2.DataFilePath is the location of the file where imported/exported data goes. Some bulk copy scenarios are handled by Transfer, but most aren't.

If you're not actually copying an entire database, you have several alternatives, none of which use SMO (which has no class for bulk copying as such):

• Directly invoking a BULK INSERT statement, which is probably what BulkCopy2 does in the background (but I haven't verified this);
• Using the SqlBulkCopy class, which will require some extra code (but which also offers far more flexibility as the data to be imported can come from any source);
• Building a command line and invoking bcp.

Of these, BULK INSERT is the most straightforward.