How to create HTML file from table on network drive in SSIS package

Question

How to create HTML file from table on network drive in SSIS package

bob-245330

Old Hand

Points: 394
More actions
May 28, 2008 at 3:57 pm

#207824

I have data in the table with fields filename,location etc. I want to create html file using all these fields. Name of the file should be filename field value. I want to create this on network drive. How can I do this in SSIS package? In sql 2000 dts I did this using activex script.
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

Why not create an SSRS report with a subscription? You may also try using your old ActiveX code inside the ActiveX Script Task in SSIS...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

bob-245330 Old Hand Points: 394 More actions · Answer 2

I tried using old script in new one. I was getting errors.

Here is my old code

Function Main()

dim sql

dim objFSO

dim objTextStream

dim strfile

dim rsdoc

dim rsline

Const fsoForWriting = 2

'get a list of the document numbers

set myConn = CreateObject("ADODB.Connection")

myConn.Open = "Provider=SQLOLEDB.1;Data Source=XXXX;Initial Catalog=XXXX;user id='Test';password='Test'"

sql = "SELECT filename FROM Temp2 WHERE Type = 'XXX' GROUP BY filename ORDER BY filename"

set rsdoc = CreateObject("ADODB.Recordset")

rsdoc.Open sql, myConn, 1

If rsdoc.recordcount > 0 Then

rsdoc.movefirst

While Not rsdoc.EOF

Set objFSO = CreateObject("Scripting.FileSystemObject")

'open the text file

strfile = "\etworkdrive\Subdir1\subdir2\" & rsdoc.Fields("filename").value & ".htm"

Set objTextStream = objFSO.OpenTextFile(strfile, fsoForWriting, True)

'write to the file, this is static text required at the beginning of every file

objTextStream.WriteLine " "

objTextStream.WriteLine " <!--"

objTextStream.WriteLine "span.cls_002{font-family:Arial,serif;font-size:9px;color:rgb(0,0,0);font-weight:bold;font-style:normal}"

objTextStream.WriteLine "div.cls_002{font-family:Arial,serif;font-size:9px;color:rgb(0,0,0);font-weight:bold;font-style:normal}"

objTextStream.WriteLine "span.cls_003{font-family:Arial,serif;font-size:9px;color:rgb(0,0,0);font-weight:normal;font-style:normal}"

objTextStream.WriteLine "div.cls_003{font-family:Arial,serif;font-size:9px;color:rgb(0,0,0);font-weight:normal;font-style:normal}"

objTextStream.WriteLine "span.cls_004{font-family:Arial,serif;font-size:8px;color:rgb(0,0,0);font-weight:normal;font-style:normal}"

objTextStream.WriteLine "div.cls_004{font-family:Arial,serif;font-size:8px;color:rgb(0,0,0);font-weight:normal;font-style:normal}"

objTextStream.WriteLine "--> "

objTextStream.WriteBlankLines(1)

objTextStream.WriteLine " "

'get all of the LineText records from PIPImport

sql = "SELECT htmText FROM Temp2 WHERE filename = " & rsdoc.Fields("filename").value & " ORDER BY LineID"

set rsline = CreateObject("ADODB.Recordset")

rsline.Open sql, myConn, 1

If rsline.recordcount >0 Then

rsline.MoveFirst

While Not rsline.EOF

objTextStream.WriteLine rsline.Fields("htmText").Value

rsline.MoveNext

Wend

End If

rsline.Close

Set rsline = Nothing

objTextStream.Close

Set objTextStream = Nothing

Set jobFSO = Nothing

rsdoc.MoveNext

Wend

End If

rsdoc.close

Set rsdoc = Nothing

Main = DTSTaskExecResult_Success

End Function

Error I got is

Retrieving the file name for a component failed with error code 0x015B8E6C.

Can some one point me in right direction.

Thank you

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 3

I would stongly suggest using SSRS...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

bob-245330 Old Hand Points: 394 More actions · Answer 4

These are not Reports. We use these files at other places. Thats why we are generating these files.