Batch File is hanging through SQL Job

SSIS and Import/Export
1

Hi Guys,

We have created one VBS file to convert the Excel file to CSV format and the given VBS file is being executed through Batch file.The batch file executes with the heop of SSIS Execute Process Task. Everything works fine through SSDT/BIDS but the package is getting hanged continuously if we try to run through SQL Job.

These are the code

Batch File :
wscript S:\Batch\vbsExceltoCSV.vbs S:\Batch\Client_200817.xlsx S:\Batch\Client_200817.csv

VBS:
Set objArgs = WScript.Arguments
InputName = objArgs(0)
OutputName = objArgs(1)
Set objExcel = CreateObject("Excel.application")
objExcel.application.visible=false
objExcel.application.displayalerts=false
set objExcelBook = objExcel.Workbooks.Open(InputName)
objExcelBook.SaveAs OutputName, -4158
objExcel.Application.Quit
objExcel.Quit

Even when I try to execute the batch file from SQL Job (CmdExec) , facing same issue.

I have tried with both Service and proxy account but no luck.

Could you please help me to identify what could be the issue.

Regards,
Gopi K

2

Probably SQL Server Agent is running under an account without access to the path or inadequate permissions to execute. Just a guess.

3

Thanks for your comment. I am executing my package through proxy account which has admin privileges and still facing this issue.

Thanks,
Gopi K

4

Try creating the output the file in csv format all in SSIS in a data flow. Dump the vbscript and use the current tools MS made for ETL. Just my opinion

5

Hi,

Just found the solution after the long time. Microsoft suggested approach is to create 'Desktop' folder in the systemprofile path. Please refer the below path for further details.

Thanks,
GopiK