Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS package error in a Query Statement Expand / Collapse
Author
Message
Posted Monday, April 6, 2009 6:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 8, 2012 8:24 PM
Points: 40, 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
Post #691590
Posted Monday, April 6, 2009 11:13 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 3,064, Visits: 2,676
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



Post #691686
Posted Friday, April 10, 2009 7:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 8, 2012 8:24 PM
Points: 40, 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

Post #695118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse