How to filter corrupted records?

[nagesh5286]

Dear all,

I am transfering records from foxpro to sql server. The problem is, I am not sure of how to filter the corrupted data.

Is there a way to do it? Furthermore, from foxpro I am tranfering nearly 70 000 rows of data. Is there any other way other than using dts?

Hope to get a reply ASAP as i am very new to the dts concept.

Thanx in advance.

Regards,
Nagesh

 

[Jamfool]

Ok I am doing something similar at the moment, and so I thought Id post you what I got so far. This is done using BCP to copy the data in to your database. This is a very rough routine so far, as ive only just started writing it.


--Produces file list from selected directory

drop table #t
create table #t(file_list varchar(8000))
insert into #t
EXEC master..xp_cmdshell 'dir c:\bcp\*.dbf'
delete from #t where (file_list not like '%.dbf' or file_list is NULL)
update #t set file_list = ltrim(reverse(Substring(reverse(file_list),1,Charindex(' ',reverse(file_list)))))

--Linked server setup

EXECUTE sp_dropserver 'DBASE_LINKER', 'droplogins'
EXEC sp_addlinkedserver 'DBASE_LINKER','Jet 4.0','Microsoft.Jet.OLEDB.4.0','c:\bcp\',NULL,'dBase IV' ;
EXEC sp_addlinkedsrvlogin 'DBASE_LINKER', 'false', NULL, NULL, NULL

Declare @filename varchar(100)
Declare @SQL varchar(5000)
declare @servername as varchar (255)
select @servername ='transfer2'
declare @filepath as varchar (255)
select @filepath ='C:\bcp\' --Path will be server relative ,nb DIRECTORY MUST EXISTS!
declare @username as varchar(255)
select @username = 'sa'
declare @userpass as varchar(255)
select @userpass = ''
declare @bcp_command varchar(255)

Declare fileCursor Cursor for select file_list from #T
OPEN fileCursor

FETCH NEXT FROM fileCursor Into @filename

While @@fetch_status = 0
Begin

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
--Links to the server, and copies data into sql server

Declare @sqlIN varchar(5000)
SET @sqlIN = 'SELECT * INTO '+left(@filename,(len(@filename)-4))+' FROM OPENQUERY(DBASE_LINKER, ''SELECT * FROM '+left(@filename,(len(@filename)-4))+''')'
EXECUTE (@sqlIN)

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

FETCH NEXT FROM fileCursor
INTO @filename

End
CLose fileCursor
Deallocate fileCursor