Excel file load in Data services

[former_member186356]

Hi Experts ,

I am trying to load the simple excel/ XLS/XLSX  or CSV file in to SQL server  using the data services. I have read many threads and they say that

if the file is on my desktop i can not upload the file in to the target using the data services . It has to be on the server .

1) How to keep the file on the server , i have asked basis team to keep the file on the server and they put it on the server and still i f i look at the log , it is unable to locate the file.

Why i can not upload the file from my desk top . How to keep the file in the folder that is accessible to the job server .

If i keep the file in the job server how does it knows the file structure . Do i still need to keep the file on the Desk top to get the structure.

What it means the "The file should be accessible to the Job server " how do i access this folder or location "   every time with out asking any ones (Basis Admin)  help. How to keep the file on the server.

2) I am trying to load the file from  FTP WINSCP  using the data services in to SQL server . Mu source file is in FTP-WINSCP and target will be

SQL server .  I am trying  to create the FTP connection using the Data access tab.  I tried different entries /connection parameters . Is there

any thing that we would like to consider since it is not working.

Thanks

Magge

[severin_thelen · Accepted solution]

Hey Kris,

to your first question. I personal never used the job server to store files. I use the server or something like that, to store the file.

The answer, that you could not load the file from your desktop is strictly speaking wrong. The thing is, that if you start the job, the DS will do work on the DS server (the machine with your DS server component). Therefore the path to the desktop shows to the server desktop. Theoretical you could set your local PC network DNS or IP in front of your path and the DS will load your file from your local desktop. (Note, that DS have to access your PC desktop, to do the job successful.)

To your second question (how to access files without admins). I would prefere a shared network drive. So the developer or a source system agent could copy the files to this folder. After that the DS could load the files and do there job.

How you use FTP in DS, I could not answer, because I never used it.

Regards

Severin

[former_member186356]

Thank you  Sevrin Nawfal Arun. We tried /do have all settings as you all mentioned , but for some reason i am unable to access the files . I think it's beyound the developer settings. I will request the Basis and linux team to check this issue. Thank you for all your answers.

Thanks

Magge

[former_member198401]

Hi Magge,

I hope the Basis and Unix team crack the issue. DS Job server on the unix server is a nightmare.

Let us know if the issue is resolved and the solution. :

Regards

Arun Sasi

[former_member186356]

Arun,

You are 1000 % right , we are having issues one after the other since the DS is on the unix and each is taking more than a week to fix .Yes , i will list out all the issues and solutions once it is done.

Thanks

Magge

[former_member198401]

Yes Magge!!

Its really a nightmare to solve the issues related to DS Job Server on Unix. But if you have a well qualified DS Unix Administrator then your life will be easy

Regards

Arun Sasi

[former_member186356]

Hi Arun & All,

We could not able to fix the issue. So now we moved the data services from Unix to Windows.

Thank you for all your answers.

Thanks

Magge

[former_member198401]

Hi Magge,

Hope you are doing good!!

As the XLSX file is on the Unix server location I see that you have full access for the file on the server(777).

Default port for FTP is 21 and needs to be open. You can test this by going to the command prompt on the job server and running the command:

telnet server_name 21

Also can you check if you are able to do a FTP to the Server location using the FTP command in command prompt.

ftp server_name

Hope you are using DS 4.2 version. Everything looks fine under the FTP Settings in the Excel Workbook format...

From the File location it seems that your BODS Job server is on unix platform. If this is the case you need to configure Excel adapter. Please refer below link. Else you can refer to DS Reference Guide.

Regards

Arun Sasi

[Former Member]

Hi,

What I can suggest is that you have 2 copies of the same source  file, one local to your client desktop and one on your job server on the Unix  server. During the development phase you create a new  file format in the local objects library and specify the location of the file to be local. With this method you can get/import and change the file format definition if necessary  and preview content. Once satisfied with the format  you should change the location of the file from "local" to  "job server" and specify the Unix path where the file resides. Make sure that the Data Services user has at least read access to the file. When executing a job, the job server will pickup the file located on the server and reads it as a source.

Regards

Nawfal