Author |
Topic |
pazzy11
Posting Yak Master
145 Posts |
Posted - 2008-05-19 : 04:23:43
|
Hi
I need to do this : I need to extract data on emails. I need send time, subject, from, to ,body ..etc..
anyway the system we have has all the info above except the body , in a table, the body is in a txt file , the txt file location is specified under body field in the table.
Now in SSIS (SQL Server 2005) i could use a script component to get the contents of the file specified in body , with DTS 2000 i'm not so sure?
I think I could use an ActiveX transformation task ? Within the Data transformation ...
what do you think ?
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-19 : 04:28:35
|
You can use ActiveX script in combination with FileSystmObject components to read file and then use CDO to send the mail.
Harsh Athalye India. "The IMPOSSIBLE is often UNTRIED" |
 |
|
pazzy11
Posting Yak Master
145 Posts |
Posted - 2008-05-19 : 07:50:10
|
Ok thanks, I don't need to send the mail, just save the body (which is saved in a txt file ) in the same file as the from,to,create_time etc info .. Im sure i can do this in ActiveX with FileSystmObject...
|
 |
|
pazzy11
Posting Yak Master
145 Posts |
Posted - 2008-05-19 : 11:59:30
|
This tutorial lets me know how I copy, delete, check if a file exists etc .. http://www.sqldts.com/292.aspx
but I wish to know how i can output the contents of the file ??
|
 |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
|
pazzy11
Posting Yak Master
145 Posts |
Posted - 2008-05-20 : 06:58:45
|
I have this [CODE] ' Visual Basic Transformation Script '************************************************************************
' Copy each source column to the destination column Function Main() Dim oFSO 'Dim sSourceFile Dim DestinationFile const fsoForReading = 1 const fsoForAppend = 8 Dim sourcefile srcpath = "\\trnoro50" & DTSSOURCE("F_NAME") Set oFSO = CreateObject("Scripting.FileSystemObject")
Set sourcefile = oFSO.OpenTextFile(srcpath, fsoForReading)
Set DestinationFile = oFSO.OpenTextFile( "C:\Shared\test_tmp\output2.csv",fsoForAppend)
DestinationFile.WriteLine(sourcefile.ReadAll)
' oFSO.CopyFile sSourceFile, sDestinationFile
' Clean Up DestinationFile.Close sourcefile.Close Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
[/CODE]
but it's giving me a problem with the DestinationFile.WriteLine(sourcefile.ReadAll) line, I need to copy the contents of one file and append it to the contents of the destination file,
|
 |
|
pazzy11
Posting Yak Master
145 Posts |
Posted - 2008-05-21 : 09:55:05
|
How can i use a file as the destination file in the package and as the destination in the script Task ? |
 |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-21 : 10:44:58
|
quote: Originally posted by pazzy11
I have this [CODE] ' Visual Basic Transformation Script '************************************************************************
' Copy each source column to the destination column Function Main() Dim oFSO 'Dim sSourceFile Dim DestinationFile const fsoForReading = 1 const fsoForAppend = 8 Dim sourcefile srcpath = "\\trnoro50" & DTSSOURCE("F_NAME") Set oFSO = CreateObject("Scripting.FileSystemObject")
Set sourcefile = oFSO.OpenTextFile(srcpath, fsoForReading)
Set DestinationFile = oFSO.OpenTextFile( "C:\Shared\test_tmp\output2.csv",fsoForAppend)
DestinationFile.WriteLine(sourcefile.ReadAll)
' oFSO.CopyFile sSourceFile, sDestinationFile
' Clean Up DestinationFile.Close sourcefile.Close Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
[/CODE]
but it's giving me a problem with the DestinationFile.WriteLine(sourcefile.ReadAll) line, I need to copy the contents of one file and append it to the contents of the destination file,
What problem you are facing here?
Harsh Athalye India. "The IMPOSSIBLE is often UNTRIED" |
 |
|
|