SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS package error in a Query Statement


DTS package error in a Query Statement

Author
Message
pavargasq
pavargasq
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 135
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
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8613 Visits: 3281
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



pavargasq
pavargasq
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 135
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search