BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar