SQL Loader : file location related error

Identity Security Cloud (ISC) ISC Discussion and Questions
,
1

Hi

I am using SQLLoader source to connect to my csv file.

I want to fetch my file from a remote windows server. My file is placed at a location like this:
C:/documents/users/user-x/Users_Dev.xml

I have created the url like this, as per the documentation:
jdbc:csv:https://host1-sandbox.acme.com/C:/documents/users/user-x

In the query string, I have provided a query like this:
select UserId, Name, Role from Users_Dev

But when I am trying to test connection, I am getting this error:

Error Received:

[ ConnectionFailedException ] [ Possible suggestions ] Verify that location provided in Data Files URL / SQL Query is correct. [ Error details ] Error occurred while establishing the connection with Data File during test configuration operation. Failed to load table Users_Dev: java.io.FileNotFoundException: https://*:*@host1-sandbox.acme.com/C%3A/documents/users/user-x/Users_Dev.xml

Any idea what could be the cause of this error?
I have tried multiple things. Nothing seems working. Is the file location related portion of the url correct?

Regards
Arshdeep

2

Hi @arshdeep_thapar , welcome to the developer community!

A few observations:

  1. Considering you are looking at a specific local path on a windows server, I’m not sure that URL looks correct. Are you able to access that file using the URL directly?

  2. Your SQL Query is looking “from” Users_Dev but your file is user-x, I believe the file name needs to match the table you are querying.

  3. Is that host name resolved by your dns and are you able to test connectivity to it from your VA directly?

  4. Lastly, try using the syntax of a shared drive or mapped drive if the server is reachable from the VA: jdbc:csv:/\IPaddress\folderpath`

I hope that helps

3

Hi @Irshaad_Laher_WS

Thanks for your response. To answer your questions:

  1. I am following this link for forming the URL and I have tried multiple combinations:

  1. My query is written to fetch the data from Users_Dev csv file, which will act as a table for SQL Loader connector.
    Also, user-x, if you look closely, is a folder and the complete path which I have mentioned is: C:/documents/users/user-x/Users_Dev.csv

  2. Yes, the host name is resolved by my dns. Actually I am using the IQService host for placing the files. Connectivity is already established and AD, IQService is working fine.

  3. As mentioned in the documentation here: Data Files URL Formatting
    I thought this is the correct way to create the url. Am I wrong here?

Regards
Arshdeep

4

You’re looking at the right documentation, but I’m certain the URL won’t resolve in that syntax with an https url and then referencing a local folder path. I think it might be worth to try the format jdbc:csv:/\IPaddress\folderpath. I got this from the IIQ for SQL Loader documentation here but I don’t see a reason this won’t work:

https://documentation.sailpoint.com/connectors/identityiq8_3/sql_loader/help/integrating_sql_loader/configuration_parameters.html#sql_loader_144685816_1840981

Considering you are using the IQ Service host, this is great, and should then be reachable using this syntax for the URL.

5

@Irshaad_Laher_WS I tried the way you mentioned, but its not working.
I don’t know what the issue is because if I do the same thing in IIQ, there the url is pre-populated like this:

jdbc:csv:/c:/data

And, I am using a similar url in ISC, with the host like this:

jdbc:csv:https://hostname/c:/data

But that didn’t work. Although sftp is not supported for windows on ISC, but if I change the url to jdbc:csv:sftp://hostname/c:/data, I get permission denied error instead of file not found error with https. Which I think is expected, given that ISC doesn’t support sftp for windows for SQL Loader.

6

For windows, you can only read files that are available through https. See:

Does your host have a web server that you can use to transfer the file?

Alicia

7

@agutschow, yes we installed one sftp service on our windows host.
Do you know if we have an sftp server on a windows host, then the sftp functionality is supported with windows?
Also, do you see any issue with the URL I am using?

jdbc:csv:https://host1-sandbox.acme.com/D:/tmp

or

jdbc:csv:sftp://host1-sandbox.acme.com/D:/tmp

I am getting an error of file not found with the former and permission denied with latter.
Any idea on that?

8

My understanding is that sftp is not supported on windows at all. The last time I used SQL Loader with sftp I had to move the file to a linux server instead of windows to get the process to work.

For https, the starting path would look like https://host1-sandbox.acme.com/ but the next part would be replaced by the path under your webserver. For example, on IIS, the default would be to put the files in c:\inetpub\wwwroot. A file in the wwwroot folder would get a URL like jdbc:csv:https://host1-sandbox.acme.com/myfile.

9

@agutschow
Thanks a lot for this information.
Test connection worked. This was really helpful.
Please correct me if I am wrong here with the resolution: for building the url using https connectivity, ISC expects a web server and the files to be placed under the server’s root folder for constructing the path.

10

It wouldn’t have to be in the root, just add any subfolders to the path like:

jdbc:csv:https://host1-sandbox.acme.com/mysubfolder/myfile

1 Like
11

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.