Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Replacing BCP with SQLBulkLoad

By Stefan Popovski,

The old good BCP easily goes to the history. XML became worldwide adopted standard for data interchange. So if you are using BCP for import text data files, and you want to use XML import files instead of text files, now is good time for change.

At first, we can see BCP utility called from xp_cmdshell procedure partial example:

DECLARE @cmd VARCHAR(256)

SET @cmd='bcp ' + @DbName + '..' + @TableName + ' in ' + @FullImportFileName + ' /S' + @ServerName + ' /f' + @FullFmtFileName + ' -T'
EXEC @Result_cmd = master..xp_cmdshell  @cmd BCP

Advantages: 

  • BCP enable  fast import for big data files
  • Database can import data without developing other application using BCP. 

Although this method for importing data is very fast, it has several limitations when we want to use themin complex data processing systems in integration with other user’s applications:

  • BCP is not appropriate for importing XML data
  • Inattention using master..xp_cmdshell can seriously endanger SQL Server security

BCP disable full control over transactions from application to the final insert in database table.

BCP was a very good utility that can help to build application independent database. What does it mean? I want see clear border between application and database. Databases have to be independent in sense that every action from application against database can be “Execute Procedure”. I don’t want any SQL statement from application code acting directly in database risking to damage database logic. In that case applications bugs will have less damage effects in database.
This is especially important if you want clear developers responsibility in your development team. In case of unexpected crash of application some procedure can be executed by hand through SQL Query Analyzer. This is reason because the task for importing data in Database I want to grant to the Database itself instead of some application or user interface.

Then we need appropriate tool for import XML files in SQL Server database, called from Stored Procedure. Although OPENXML statement can be used for direct import in database, I prefer this option:

Using SQLXMLBulkLoad.SQLXMLBulkLoad.3.0

On your SQL Server you have to install SQLXML3.0 SP1 (http://msdn.microsoft.com), then create the file ImportData.xml in 'C:\Folder\ImportData.xml' ImportData.xml using the following data:

<ImportData>
	<Row>
		<Field1>Row1_ Filed1_Data</Field1>
		<Field2>Row1_ Filed2_Data</Field2>
		<Field3>Row1_ Filed3_Data</Field3>
	</Row>
   	<Row>
   		<Field1>Row2_ Filed1_Data</Field1>
   		<Field2>Row2_ Filed2_Data</Field2>
   		<Field3>Row2_ Filed3_Data</Field3>
   	</Row>
</ImportData>

You also need to create a file called schema.xml in the same folder, as follows:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <ElementType name="Field1" dt:type="string"/>
   <ElementType name="Field2" dt:type="string"/>
   <ElementType name="Field3" dt:type="string"/>
   <ElementType name="ElementRow" sql:is-constant="1">
   <element type="Row"/>
   </ElementType>
   <ElementType name="Row"  sql:relation="TableImport">
   <element type="Field1"  sql:field="TabField1"/>
        <element type="Field2"  sql:field="TabField2"/>
        <element type="Field3"  sql:field="TabField3"/>
   </ElementType>
</Schema>

Create TableImport1 in “YourDatabase” executing the following script SQL Query Analyzer:

	CREATE TABLE [dbo].[TableImport1] (
	[TabField1] [varchar] (40) NULL,
	[TabField2] [varchar] (40) NULL ,
	[TabField3] [varchar] (40) NULL) ON [PRIMARY]
	GO

Then you can create new procedure in you database:

CREATE PROCEDURE BulkLoad AS 

DECLARE	@objectINT
DECLARE @hr INT
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR (255)
DECLARE @Schema VARCHAR(128)DECLARE @ImportDataVARCHAR(128)
DECLARE @ErrorFile VARCHAR(128)

SET @Schema = 'C:\Folder\schema.xml'SET @ImportData =  'C:\Folder\ImportData.xml'SET @ErrorFile =
'C:\Folder\Error.log'

EXEC @hr = sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkLoad.3.0', @object OUT
IF @hr <> 0BEGIN
	EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc   RETURNEND
ELSE
	EXEC @hr = sp_OASetProperty @object, 'ConnectionString', 'provider=SQLOLEDB.1;data source=SERVERNAME; database= YourDatabase;Trusted_Connection=Yes'

IF @hr <> 0
	BEGIN
		PRINT 'ERROR sp_OAMethod - ConnectionString'                                      		EXEC sp_OAGetErrorInfo @object
		RETURN
	END

	EXEC @hr = sp_OASetProperty @object, 'ErrorLogFile', @ErrorFile
	IF @hr <> 0
    		BEGIN
	    		PRINT 'ERROR sp_OAMethod - ErrorLogFile'
   		 	EXEC sp_OAGetErrorInfo @object
	    		RETURN
	    	END
   	
   	EXEC @hr = sp_OAMethod @object, 'Execute', NULL, @Schema, @ImportData
	IF @hr <> 0
		BEGIN
			PRINT 'ERROR sp_OAMethod - Execute'
			EXEC sp_OAGetErrorInfo @object
			RETURN
		END

	EXEC @hr = sp_OADestroy @object
	IF @hr <> 0
		BEGIN
			PRINT 'ERROR sp_OADestroy '
			EXEC sp_OAGetErrorInfo @object
			RETURN
		END
   SELECT  'OK'
GO

At last you are prepared to execute your procedure to import data into TableImport1 by executing the 'BulkLoad' procedure from SQL Query Analyzer. Don’t forget to change connection string appropriate to your server (the one shown uses a trusted connection, but you could change to use a sql login). After executing you can test if it was successful by doing a select * from TableImport1, which should yield three rows.

Total article views: 11987 | Views in the last 30 days: 11
 
Related Articles
FORUM

Database Object Permissions Lost

Database Object Permissions Lost

FORUM

excel is not importing into an application

excel is not importing into an application

FORUM

Database objects deployment

Database objects deployment

ARTICLE

Is Your Database Application DeadLock and Timeout Resistent?

Is your application scalable under increased activity? Timothy Claason brings us a methodology for t...

ARTICLE

Why Object Qualification is important.

You have all read about why object qualification is important. You also must have heard of why store...

Tags
miscellaneous    
t-sql    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones