Convert a File extension via SSIS

Question

I have a file that is delivered to a folder via an SAP Back-end job. It is delivered as a .MHTML file. I dont see any of the Source Connections in SSIS that will accept this extension.

How can I convert this extension before i start the SSIS package. I just want to convert it to a .xls or .xlxs so that I can set an Excel Source connection.

The file saves over the existing every month so no worry of file name changes or anything like that. The file simple comes in as export.mhtml

How is the data structured in the mhtml file? Is the file just comprised of a html table or is it more complex? — richardpilgrim, May 07 '14 at 10:11
It's an Excel Table. It's just been saved with the .MHTML extension. When you open it it opens as an Excel File in the Internet Explorer window. — Brian D. Brubaker, May 07 '14 at 12:41

TMNT2014 · Answer 1 · 2014-05-06T16:13:18.107

1

If you are sure that just changing the extension to .xls or .xlsx would let you consume the data then you can use a file system task to rename the file with a different extension or use the task to copy the file to a different destination folder with a different extension.

edited May 06 '14 at 16:13

answered May 06 '14 at 16:08

TMNT2014

2,102
1
11
13

score 1 · Answer 2 · edited May 23 '17 at 12:05

Here is a possible solution.

To convert the file from an mhtml document to a more SSIS friendly format you can use this VBS script (adapted from Convert XLS to CSV on command line).

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 50 '50 for normal xls or 6 for csv

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

Save this file as mhttoxls.vbs or similar and you can run it as shown below.

mhttoxls.vbs [sourcemhtFile].mhtml [destinationxlsfile].xls

You can run it in SSIS using an "Execute Process" task that species the executable as cmd.exe and the arguments as

/c "mhttoxls.vbs [sourcemhtFile].mhtml [destinationxlsfile].xls"

Make sure you also set the correct working directory for your files.

Once you have your xls file then you can setup an Excel source connection in a Data Flow task to import your data.

I hope this helps!

Convert a File extension via SSIS

2 Answers2