April 6, 2009 at 6:45 pm
Good evening, need your help
in DTS ive got a ActiveScripTask which must prefotm the following:
1. In an textfile. called list.txt which contains a set of records ie:
list.txt
file1_abc
file2.xcv
file3.xx
2. i want to validate if everyline (record) was loaded in a database table.
All is going fine if theyre found (no mistakes made) but;
if any record file ie (file2.xcv) is not found then the whole package fails with the error:
Error.ADODB:recordset Either BOF or EOF is true or the current record has been deleted.
This is the code in ActiveScrpitX Task
dim OConn,fso,f1,txt,recordline
dim rscategory
Const ForReading=1
set OConn = CreateObject("ADODB.Connection")
conn_string="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=DATAMARTSQL;Application Name=Pruebas"
OConn.Open= conn_string
set fso = CreateObject("Scripting.FilesystemObject")
set f1 = fso.GetFolder( "\\bogccswbavaria\Documentos\Pablo")
set txt = fso.OpenTextFile("\\bogccswbavaria\Documentos\Pablo\lista.txt",ForReading)
do until txt.AtEndofStream
recordline=txt.Readline
Set rsCategory= OConn.Execute("Select * from archivos_pruebas where nombre_archivo like '%"&recordline&"'")
MsgBox rsCategory.GetString
loop
txt.close
set fso=Nothing
OConn.Close
set OConn=Nothing
Set rsCategory= Nothing
Main = DTSTaskExecResult_Success
Any idea that comes up from you Ill appreciate your help
April 6, 2009 at 11:13 pm
The error you are getting suggests that you should be checking whether the SELECT statement returns anything before you try to use it (in your msgbox)....
Been a while since I coded anything using ADO (or vbscript) but I think that there is an EOF method. Try the following:
dim OConn,fso,f1,txt,recordline
dim rscategory
Const ForReading=1
set OConn = CreateObject("ADODB.Connection")
conn_string="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=DATAMARTSQL;Application Name=Pruebas"
OConn.Open= conn_string
set fso = CreateObject("Scripting.FilesystemObject")
set f1 = fso.GetFolder( "\\bogccswbavaria\Documentos\Pablo")
set txt = fso.OpenTextFile("\\bogccswbavaria\Documentos\Pablo\lista.txt",ForReading)
do until txt.AtEndofStream
recordline=txt.Readline
Set rsCategory= OConn.Execute("Select * from archivos_pruebas where nombre_archivo like '%"&recordline&"'")
IF rsCategory.EOF Then
MsgBox ("No record found")
Else
MsgBox rsCategory.GetString
End if
loop
txt.close
set fso=Nothing
OConn.Close
set OConn=Nothing
Set rsCategory= Nothing
Main = DTSTaskExecResult_Success
April 10, 2009 at 7:26 pm
ok, I tried and its working.
The only thing additional ive got to do is to create the Recordst Object, open it, then call the EOF method, thank you very much again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply