Executing .vbs file error in Data Services

dataservices job design
1

Hi Experts,

I’ve been trying to resolve this issue for the past week and it is proving difficult.

I have a simple vb script that converts all .xls files to .csv in specified location (i.e. c:\temp). When I double click the .vbs file, it works beautifully. However when I try to run this from Data Services using exec command. It behaves really strange.

Firstly the .vbs file contains:

WorkingDir = "C:\Temp"
Extension = ".XLS"

Dim fso, myFolder, fileColl, aFile, FileName, SaveName
Dim objExcel,objWorkbook

Set fso = CreateObject("Scripting.FilesystemObject")
Set myFolder = fso.GetFolder(WorkingDir)
Set fileColl = myFolder.Files

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False
objExcel.DisplayAlerts= False

For Each aFile In fileColl
	ext = Right(aFile.Name,4)
	If UCase(ext) = UCase(extension) Then
		'open excel
		FileName = Left(aFile,InStrRev(aFile,"."))
		Set objWorkbook = objExcel.Workbooks.Open(aFile)
		SaveName = FileName & "csv"
		objWorkbook.SaveAs SaveName, 23
		objWorkbook.Close 
	End If	
Next

Set objWorkbook = Nothing
Set objExcel = Nothing
Set fso = Nothing
Set myFolder = Nothing
Set fileColl = Nothing

I’ve tried using these commands in Data Services

 exec('cscript','c:\\temp\\test.vbs',0);
 exec('wscript','c:\\temp\\test.vbs',0);

I even tried creating a .bat file which calls this .vbs file.

Everything I do, it did’nt work and errors I receive were different depending on what I used.

If I use ‘cscript’ it gives me the following error:


5608	5488	PRINTFN	30/05/2012 2:04:44 PM	c:\temp\convert_REF.vbs(21, 3) Microsoft Excel: Microsoft Excel cannot access the file 'C:\Temp\test.XLS'. There are several
5608	5488	PRINTFN	30/05/2012 2:04:44 PM	possible reasons:      The file name or path does not exist.    The file is being used by another program.    The workbook you
5608	5488	PRINTFN	30/05/2012 2:04:44 PM	are trying to save has the same name as a currently open workbook.

If I use ‘wscript’ the job runs and doesn’t do anything until I kill it manually.

I initially thought that this is a security previledge issue but I can see that all Data Services account has full previledges. Let alone the file location is in the ETL box itself!

I ran out of all options. I think I’ve tried everything I can ever think of.

Could you please help.

Thanks alot

Jamesy121 (BOB member since 2009-05-04)

2

2 things.

exec(‘cscript’,‘c:\temp\test.vbs’,8); # Try this with value 8 which would tell all the errors

Other one I bet this is a privilege issue? You said you could double-click execute VB Script…How it runs? From the Job Server machine or local machine? Looks like you are trying this out and C:\TEMP is your local (client) machine path. So basically when the job runs, it is running from the server and not from your local machine. And other, it is not using your account, it is using the account with which the service runs on!!! So login services.msc and see on what account the SAP Data Services service runs on???

ganeshxp :us: (BOB member since 2008-07-17)

3

and excel must be installed on the job server for this to work.

jlynn73 :us: (BOB member since 2009-10-27)

4

Hi, have the same issues, did you found a solution.
my Dataservices is working with a windows account, the same account run correctly the script but from dataservice it does not work, plz help.

challenger212 (BOB member since 2012-07-11)

5

im also facing the same issue
when i execute batch file from bods such as renaming one file,it works .
but when im executing batch file containing code to execute vbscript it doesnt work.
when i double click on batch file it works properly !!
please reply

pratik070793 (BOB member since 2016-11-10)