Cannot connect to Flat file or excel workbook in DI

dataservices job design
1

Hello

I have created a simple job to read data from excel file and target is a template table.
I am using DI 3.0.

I have tried using flat files. I set the source as local, and put the path as ‘C\Data’ and filename as filename
But when I try to run the job, it says

'Cannot Open file <C/Data/filename.csv' Please check its path and permissions

It throws error 80101

Now, I have given all the permission on the file and the folder.

I have tried doing a similar thing with the "Excel workbook’ feature in DI and try using that file in the job
But I get the same error

Am I missing something. :hb: :hb:

namitrs :us: (BOB member since 2006-11-28)

2

Is the file on your PC’s C: drive, or on the Job Server’s C: drive? It has to be the latter, since the DI code is executing on that remote server.

So you need to either copy the file to the server first (manually or via a script/batch file), or, set up the job to read over the network and not put the file on your PC. Put it on a network file share/folder someplace.

dnewton :us: (BOB member since 2004-01-30)

3

The file is on the C:\Data folder, which is a shared folder and everyone has access to it.

My PC is on the network, and the folder the file stays on is a shared folder with everyone having access to it. So then why isnt the job server able to connect to it then.

All the times before even I have connected when the file resides on the Job Server C Drive. But this time, we have situation where the file cannot be on the Job server.

namitrs :us: (BOB member since 2006-11-28)

4

C:… is obviously not the shared drive name, it is the local name. local to the jobserver. So use the shared directory path instead \host\share\dir\file.

And regarding permissions, read this: https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Shared+Directory+access

Werner Daehn :de: (BOB member since 2004-12-17)

5

That’s right. I shared the folder, defined the flat file to be Local, and gave the path as
\host\shared dir name
And in the file name input - gave the file name.csv

And yeah I read the link you provided. But i have provided ‘EVERYONE’ access to the share. That means whatever logon account from Jobserver will try to connect to my local share, it would have access to do it.

I am getting tempted to try the code mentioned in that link. Will try that as well.

namitrs :us: (BOB member since 2006-11-28)

6

Even I have the same issue. I have shared the folder in which my flat file reside.

I tried using the code that has been mentioned in the link. But I am getting error. Below is the code that I am using:

exec(‘cmd’,‘net use \\myLocalMachine\DestinationFolder /MyUserID:MyDomain\MyPassword’,0);

Is the syntax is correct?

Can anyone help?

Karthik LY (BOB member since 2009-02-25)

7

I want to revive this thread as I am having this problem with BODS 4 as well.
I have to ftp files from an ftp server to the jobserver using WinSCP as custom transfer program.

I place the files in C:\Temp on the jobserver and when it is a .TXT file it works perfectly but when it is a .CSV file it fails with cannot open file C:/Temp/abc.csv check path and permissions. It seems clear the permissions are not the problem as both files are transferred and created in the same folder using the same user.

The interesting part is that when switching on all trace messages the .txt files shows the root directory as C:\Temp while the .csv file shows the root directory as C:/Temp. The path is definitely C:\Temp not C:/Temp.

I have searched high and low and can not find a reason why DS changes the “” to “/” when dealing with a .csv file that has been transferred through ftp.

I did test by creating a .csv file locally and that works fine, also a .csv file copied via windows to the job server works fine. It is only when a csv file has been copied using WinSCP that the path is changed as above.

Any ideas will be appreciated as I have to use files on the secure ftp server as input.

johann_cornelius (BOB member since 2009-09-02)

8

I did a further test, if I create a csv in windows and copy it to the ftp server and then copy it back, it works. It seems the problem is if the file has been created in unix and then placed on the ftp server it somehow retains the unix path description (as far as BODS is concerned) even when you place it in a windows directory structure.

My problem remains the same, how to tell BODS that it is not a unix file but a windows one?

johann_cornelius (BOB member since 2009-09-02)

9

Hi,

I have a problem accessing the .xls file when placed on job server. When I place the .txt file then it reads them properly, however when I place the data in .xls file then it give error.

It gives me below error,

(12.1) 11-25-13 06:58:20 (E) (20646:0001) FIL-052008: |Dataflow DF_Excel_1
FTP could not transfer file </ora/ftp/import/Sanofi_Rgn_Sample_data.xls> from host :
</ora/ftp/import/Sanofi_Rgn_Sample_data.xls: Permission denied>.
Please ensure that the FTP relative path for the SAP R/3 working directory is set correctly such that the data file is
accessible to the FTP program. Also, ensure that the target path, where the transferred file is written to, is correct.

Attach are the screenshots of my Excel file format, let me know if something is wrong while setting up my Excel file format.

Thanks for your time.

Dvn (BOB member since 2012-11-24)