Active X Transformation task..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Active X Transformation task..

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

Posted - 2008-05-19 : 12:58:45

Check if this helps:
http://www.4guysfromrolla.com/webtech/faq/FileSystemObject/faq3.shtml

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources