COMWrapper error in SSIS script task while converting an .xls document to CSV

Question

I hope someone can help me with my SSIS automation error. I created a package that downloads a file from a webportal. In second step of my package, i convert that .xls to .csv(there is a valid reason for this conversion as downloaded .xls file can't be opened by package). I use below code to convert my File to csv and works perfectly fine on my local machine. But when i deploy it on server. It throws below error message. The only thing i could think of is that when i run it on my local machine, when it converts the file to csv, a message box pops up on the screen saying "converting the file" and then "saving the file as .csv". And server machine is having issues to deal with these message boxes ? Help will be appreciated.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Net;
#endregion

    public void Main()
    {
        // TODO: Add your code here

        Excel.Application excelapp = new Excel.Application();
        Excel.Workbook Datasource = (Excel.Workbook)excelapp.Workbooks.Add(1);

        var DownloadPath = Dts.Variables["User::varDownloadPathNew"].Value.ToString();
        var ConvertedPath = Dts.Variables["User::varConvertedFileName"].Value.ToString();

        string FileName = DownloadPath;
        var format = Excel.XlFileFormat.xlCSV;

        Datasource = excelapp.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        Datasource.SaveAs(ConvertedPath, format, Missing.Value, Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
        Excel.XlSaveConflictResolution.xlUserResolution, true,
        Missing.Value, Missing.Value, Missing.Value);

        //Clean
        Datasource.Close(true);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Datasource);

        excelapp.Quit();
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelapp);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Could you check that the package is running under the same bitness on the server as the version of excel installed? i.e 32 or 64? — Mark Wojciechowicz, Mar 19 '18 at 14:24

score 0 · Answer 1 · answered Apr 13 '18 at 10:38

Managed to resolve the issue eventually. Ended up creating below two folders on C drive and then restarting the Server machine after that (Note,i installed MS office on server machine first).

C:\Windows\System32\config\systemprofile\Desktop C:\Windows\SysWOW64\config\systemprofile\Desktop

This resolved the issue for me.

Thanks

COMWrapper error in SSIS script task while converting an .xls document to CSV

1 Answers1