The requiement is to create excel file. I cannot run this code using SSIS vbscript task because this requires Microsoft.Office.Interop DLL which can not be installed on the dev / prod server. Hence using the SQL task the data exported to excel.
But now the excel is required to be updated to merge some cells. The approach we are thinking is to keep VBS file with required code to UNC path. Keep the BAT file running VBS file using CSCRIPT command in same location. Call the BAT file from SSIS package
using Execute Process Task. This approach is tested in local system and also works on dev server. But somehow the sample code used to create excel is not creating excel to UNC path.
VBS code below
Const xlSaveChanges = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Cells(1, 1).Value ="Test value"
The BAT used to call above VBS file is given below
The above UNC path is used in execute process task package which runs fine. But the file is not yet created.
View Complete Post