BULK INSERT data conversion error (type mismatch)

CREATE PROC [dbo].[usp_procname2]
AS
SET NOCOUNT ON
/* Allows for the bulk insert of the eod filename.csv's, which is done by the 1st step of the 'JOB NAME' SQLAgent job.
 - Any/all filename%.csv filenames found at \SHARENAME are loaded first to dbo.filenametable.
 - This table is then referenced for the subsequent bulkload into WorkingTable.  
 - Any/All dupes are flagged in dbo.WorkingTable;  all non-flagged data is then loaded in batches to dbo.TargetTable.
 - The flagged violations are then written to dbo.TargetTableDupes, and dbo.WorkingTable is truncated.
 - Thus, we've now avoided the recurring failures due to dupes found in the filename file(s) because of problems upstream at the source(s).
 
EXEC dbo.procname2 */
BEGIN
	DECLARE @FileName varchar(500), @SQL varchar(3000), @Path varchar(200)
 
	/* 1.  Clean out/reload dbo.filenametable. */
	DELETE dbo.filenametable
	SET @Path = '\\servername\sharename\'
	EXECUTE dbo.usp_ListFiles @Path,'dbo.filenametable','%filename%.csv',NULL,0
 
	/* 2. IF EXISTS, CURSOR through tbl, load all files (filenames) from step one to dbo.WorkingTable. */
	IF EXISTS(SELECT 1 FROM dbo.filenametable)
	BEGIN
		DECLARE db_cursor CURSOR FOR  
		SELECT FileName FROM dbo.filenametable
		
		OPEN db_cursor   
		FETCH NEXT FROM db_cursor INTO @FileName   
 
		WHILE @@FETCH_STATUS = 0   
		BEGIN   
			DECLARE @rowcount1 INT
			SET @SQL = 'BULK INSERT dbo.WorkingTable FROM ' + CHAR(39) + @Path + @FileName + CHAR(39) + ' WITH(FORMATFILE = ''E:\MSSQL\Tools\TargetTable.fmt'',FIELDTERMINATOR='','',ROWTERMINATOR=''\r'',FIRSTROW=1,TABLOCK,ROWS_PER_BATCH=100000)'
			EXECUTE (@SQL) 
 
			SET @rowcount1 = @@ROWCOUNT
			SELECT CONVERT(VARCHAR,@rowcount1) + ' Records bulk inserted to dbo.WorkingTable from ' +@FileName +'.'
 
	        FETCH NEXT FROM db_cursor INTO @FileName  
		END
			CLOSE db_cursor   
			DEALLOCATE db_cursor 
	END
	ELSE
		BEGIN
		PRINT 'The filename files have not been located.'
		EXEC master..xp_sendmail @recipients ='emailaddresses',@subject='JOB NAME',@message='The filename .csv''s have not been located.'
		SELECT @@ROWCOUNT -- simply in place for the java layer, to avoid a sql exception
		RETURN
	END
 
	/* 3a.  Now let's check and flag any internal dupes dbo.WorkingTable, internally first (keeping most recent). */
	DECLARE @rowcount2 INT
	UPDATE dbo.WorkingTable SET status_flag = 1
	WHERE dbo.WorkingTable.OMID <> (SELECT MAX(d.OMID) FROM dbo.WorkingTable d
	WHERE d.OrderNo = dbo.WorkingTable.OrderNo AND d.ClorID = dbo.WorkingTable.ClorID)
	OPTION (MAXDOP 1)
	SET @rowcount2 = @@ROWCOUNT
	SELECT CONVERT(VARCHAR,@rowcount2) + ' Records updated on 1st dupe update.'
 
	/* 3b. Now let's check and flag any dupes in comparison to the already-existing TargetTable data. */
	DECLARE @rowcount3 INT
	UPDATE dbo.WorkingTable SET status_flag = 1
	FROM dbo.WorkingTable
	INNER JOIN dbo.TargetTable d ON d.OrderNo = dbo.WorkingTable.OrderNo AND d.ClorID = dbo.WorkingTable.ClorID
	OPTION (MAXDOP 1)
	SET @rowcount3 = @@ROWCOUNT
	SELECT CONVERT(VARCHAR,@rowcount3) + ' Records updated on 2nd dupe update.'
 
	/* 4. Insert from dbo.WorkingTable to dbo.TargetTable where OrderNo,ClorID doesnt exist... 
	 ( Not dropping the indices w/TargetTable.  Present volumes do not warrant it, 12/3/08. )  */
	DECLARE @rowcount4 INT
	BEGIN TRANSACTION
	WHILE @@ROWCOUNT >0
	SET ROWCOUNT 500000
 
	   INSERT dbo.TargetTable (..........fields........)
	   SELECT w.field1,w.field2,.......w.firmposition,w.field5,w.field6.......
	   FROM dbo.WorkingTable w WITH (NOLOCK)
	   WHERE w.status_flag = 0
	   OPTION (MAXDOP 1)
 
	SET @rowcount4 = @@ROWCOUNT
	SET ROWCOUNT 0
	SELECT CONVERT(VARCHAR,@rowcount4) + ' Records inserted to dbo.TargetTable.'
 
	IF @@ERROR = 0
	BEGIN
		COMMIT TRAN
	END
	ELSE
	BEGIN
		ROLLBACK TRAN
	END
 
	/* 5.  First write WorkingTable to TargetTableDupes, then truncate the working table. */
	DECLARE @rowcount5 INT
	BEGIN TRANSACTION
	WHILE @@ROWCOUNT >0
	SET ROWCOUNT 100000
 
  	INSERT dbo.TargetTableDupes(..........fields........)
	SELECT w.field1,w.field2,.......w.firmposition,w.field5,w.field6.......
	FROM dbo.WorkingTable w WITH (NOLOCK) WHERE status_flag = 1
	AND NOT EXISTS (SELECT 1 FROM dbo.TargetTableDupes d WHERE w.OrderNo = d.OrderNo AND w.ClorID = d.ClorID)
 
	SET @rowcount5 = @@ROWCOUNT
	SET ROWCOUNT 0
	SELECT CONVERT(VARCHAR,@rowcount5) + ' Records written to dbo.TargetTableDupes.'
 
	IF @@ERROR = 0
	BEGIN
	  TRUNCATE TABLE WorkingTable
	  COMMIT TRAN
	END
	ELSE	
	BEGIN
	  ROLLBACK TRAN
  	END
    /*	 6. EXEC the bat file to rename/relocate the current .csv's. */
	EXEC master..xp_cmdshell '\\servername\E$\MSSQL\Tools\filename.bat', no_output  */
END
 
SET NOCOUNT OFF
GO

Select allOpen in new window