Need some help about DTS

I have create a DTS package, This job is fist connect to database, and
then excute a SQL query, then use the VB ActiveX to create a procedure
to store the sql result to a text file. I ran the package there is no
porblem with query.But the second step for output the result to text
file have problem. The test fiel become 200mb big and cannot open. I
would like to know if there something wrong with my programming.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
On Error Resume Next
Dim strRecord
Dim strEmailBody
Dim objFSO
Dim objStream
Dim objResults
Const OUTPUT_FILE = "c:\CompanyList.txt"
Const fsoForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objResults = DTSGlobalVariables("gResults").Value
Set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, True)
'Loop through the records and output each one
'to a file.
While Not objResults.EOF
strRecord = "Relatiecode #" & objResults.Fields(0).Value & _
"JA/NEE" & objResults.Fields(1).Value
objStream.WriteLine (strRecord)
objStream.WriteBlankLines (1)
objResults.MoveNext
Wend
If Err.Count = 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End FunctionHi
"superbaby@.gmail.com" wrote:
> I have create a DTS package, This job is fist connect to database, and
> then excute a SQL query, then use the VB ActiveX to create a procedure
> to store the sql result to a text file. I ran the package there is no
> porblem with query.But the second step for output the result to text
> file have problem. The test fiel become 200mb big and cannot open. I
> would like to know if there something wrong with my programming.
> '**********************************************************************
> ' Visual Basic ActiveX Script
> '************************************************************************
> Function Main()
> On Error Resume Next
> Dim strRecord
> Dim strEmailBody
> Dim objFSO
> Dim objStream
> Dim objResults
> Const OUTPUT_FILE = "c:\CompanyList.txt"
> Const fsoForWriting = 2
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objResults = DTSGlobalVariables("gResults").Value
> Set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, True)
> 'Loop through the records and output each one
> 'to a file.
> While Not objResults.EOF
> strRecord = "Relatiecode #" & objResults.Fields(0).Value & _
> "JA/NEE" & objResults.Fields(1).Value
> objStream.WriteLine (strRecord)
> objStream.WriteBlankLines (1)
> objResults.MoveNext
> Wend
>
> If Err.Count = 0 Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> End Function
>
You don't seem to be closing the file and setting your cleaning up the
objects by setting them to nothing for examples see
http://www.sqldts.com/303.aspx
I am not sure if it will not solve the problem though!!
John

Need some help about DTS

I have create a DTS package, This job is fist connect to database, and
then excute a SQL query, then use the VB ActiveX to create a procedure
to store the sql result to a text file. I ran the package there is no
porblem with query.But the second step for output the result to text
file have problem. The test fiel become 200mb big and cannot open. I
would like to know if there something wrong with my programming.
'***************************************
*******************************
' Visual Basic ActiveX Script
'***************************************
*********************************
Function Main()
On Error Resume Next
Dim strRecord
Dim strEmailBody
Dim objFSO
Dim objStream
Dim objResults
Const OUTPUT_FILE = "c:\CompanyList.txt"
Const fsoForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objResults = DTSGlobalVariables("gResults").Value
Set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, True)
'Loop through the records and output each one
'to a file.
While Not objResults.EOF
strRecord = "Relatiecode #" & objResults.Fields(0).Value & _
"JA/NEE" & objResults.Fields(1).Value
objStream.WriteLine (strRecord)
objStream.WriteBlankLines (1)
objResults.MoveNext
Wend
If Err.Count = 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End FunctionHi
"superbaby@.gmail.com" wrote:

> I have create a DTS package, This job is fist connect to database, and
> then excute a SQL query, then use the VB ActiveX to create a procedure
> to store the sql result to a text file. I ran the package there is no
> porblem with query.But the second step for output the result to text
> file have problem. The test fiel become 200mb big and cannot open. I
> would like to know if there something wrong with my programming.
> '***************************************
*******************************
> ' Visual Basic ActiveX Script
> '***************************************
*********************************
> Function Main()
> On Error Resume Next
> Dim strRecord
> Dim strEmailBody
> Dim objFSO
> Dim objStream
> Dim objResults
> Const OUTPUT_FILE = "c:\CompanyList.txt"
> Const fsoForWriting = 2
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objResults = DTSGlobalVariables("gResults").Value
> Set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, True)
> 'Loop through the records and output each one
> 'to a file.
> While Not objResults.EOF
> strRecord = "Relatiecode #" & objResults.Fields(0).Value & _
> "JA/NEE" & objResults.Fields(1).Value
> objStream.WriteLine (strRecord)
> objStream.WriteBlankLines (1)
> objResults.MoveNext
> Wend
>
> If Err.Count = 0 Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> End Function
>
You don't seem to be closing the file and setting your cleaning up the
objects by setting them to nothing for examples see
http://www.sqldts.com/303.aspx
I am not sure if it will not solve the problem though!!
John

Need Select Query

Hi to all
I Need a sql Qery for Text files.
Fist I want to know will it be possible to select the All Filelds fromText file...?
I just thoght of like
Select * from MyTextFile.Txt
Please Help me.to my knowledge there isn't a way to do this via Transact SQL. If you have access to the xp_cmdshell stored procedure yo umight try something like:

Code:
------------------------------
master.dbo.xp_cmdshell 'type MyTextFile.Txt'
------------------------------

Of course you will need to fully qualify the path to the text file as SQL server would assume (based on the above) that MyTextFile.Txt is on drive C: of your SQL server.|||I just tried like this
master.dbo.xp_cmdshell 'type C:\Sample.Txt'

I got the message as follows

outpur
The system cannot find the file specified.
NULL

(2 row(s) affected)|||again, xp_cmdshell will look on your sql server for the file NOT your local PC. Try this and see what you get:

code:
------------------------------
master.dbo.xp_cmdshell 'dir'
master.dbo.xp_cmdshell 'type c:\boot.ini'
------------------------------|||Thanks a lot
It is workig fine..