topic Re: Qliksense web connector [SFTP] failed in Integration, Extension & APIs

Qliksense web connector [SFTP] failed

Deepak_dhikale — Mon, 19 Jul 2021 05:51:33 GMT

Hello all

I need to fetch some files from SFTP server to Qliksense through Qlik Web connector. but While installation I forgot to install it with Run as Administrator . So When I put required credentials' in SFTP page its shows me below error regarding deply.config file . for which I made required changes in deploy.config file but it states that u need to restart the QWC .so when i was trying to restart QWC services it again pop up with error.[which i attached below] and it also not allowing me to delete whole qlik webconnector file for fresh installation saying that it is in run state.

Please guide me what should i do

thanks in advance

regards

Deepak D

India

Qlik Web Connectors

Re: Qliksense web connector [SFTP] failed

NadiaB — Mon, 19 Jul 2021 12:37:46 GMT

Hi @Deepak_dhikale

I was not able to see the error the image is very small, could you type the error in the description of the issue or attach a bigger picture?

Thank you in advance.

Re: Qliksense web connector [SFTP] failed

Deepak_dhikale — Wed, 21 Jul 2021 09:53:30 GMT

Hi @NadiaB

Appreciated your instant reply.

The issue now resolved now. But come up with new challenge now. I Have multiple files over sftp server. Every week there are two files created with new name. I Have two URL 1) sftpdownloadandlist 2) singlerawfile I ma working on below code. I want to fetch all files with single connection and want to store them into qvd.here is below code which has no error but no output to except [ path and file name]

I want to fetch data from table.

let vQwcConnectionName = 'lib://sftp_url (allenlund_nprinting)';
let vDirectory = '/home/QlikServer/outbox/';
let vFtpBaseUrl = 'http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPDownloadAndList&host=harmony-la.allenlund.com&port=5022&userName=QlikServer&password=sta_gen_1%3aekhHROY5hOEHHqLj%2fHgFaQ%3d%3d%3aQ5a0YHe5UOEN%2fkNHJBVKuw%3d%3d&ignoreProxy=False&ignoreClientCertificateErrors=False&useLegacyGroupExchange=False&remotePath=%2fhome%2fQlikServer%2foutbox%2f*.*&targetDirectory=E%3a%5ctest&appID=';
let vftpsinglefileurl ='lib://sftp_url_single_file (allenlund_nprinting)';

FilesAndFolders:
LOAD
FileName ,
Path
FROM [$(vQwcConnectionName)]
(URL IS [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPDownloadAndList&host=harmony-la.allenlund.com&port=5022&userName=QlikServer&password=sta_gen_1%3aekhHROY5hOEHHqLj%2fHgFaQ%3d%3d%3aQ5a0YHe5UOEN%2fkNHJBVKuw%3d%3d&ignoreProxy=False&ignoreClientCertificateErrors=False&useLegacyGroupExchange=False&remotePath=%2fhome%2fQlikServer%2foutbox%2f*.*&targetDirectory=E%3a%5ctest&appID=], qvx);

let vDirectoryEncoded = replace(vDirectory, '/', '%2f');
vDirectoryEncoded = replace(vDirectoryEncoded, ' ', '+');

LET vNoOfFiles = NoOfRows('FilesAndFolders');

for i=0 to $(vNoOfFiles)-1

if(peek('IsDirectory', $(i), 'FilesAndFolders') = 'False') then

let vFilePath = peek('Path', $(i), 'FilesAndFolders');
let vFilePathEncoded = replace(vFilePath, '/', '%2f');
vFilePathEncoded = replace(vFilePathEncoded, ' ', '+');

trace 'Loading file $(vFilePath)';

Data:
LOAD
'$(vFilePath)' as Path,
PRACTICAL_MILEAGE,
RATE_DATE,
YRMO,
DCITY_INDEX
FROM [lib://vQwcConnectionName ]
(URL IS [$(vFtpBaseUrl)&table=SFTPGetRawFile&remotePath=$(vFilePathEncoded)]);

endif

next i

Thank you

In Advance

Deepak D

Re: Qliksense web connector [SFTP] failed

stevedark — Wed, 21 Jul 2021 13:00:09 GMT

Hi @Deepak_dhikale

What I would suggest is that you separate out the retrieval of the files from the FTP and the building of the data-layer and visualisations.

Rather than calling the SFTPGetRawFile method I would opt for SFTPDownload instead. With this you need to provide a local file path to write the file to, and note that you need to modify your QWC deploy.config file to whitelist the location that you intend to write to.

To make this a better incremental approach I would put a check for the file already existing before you download. This can be done like this:

if alt(FileSize('lib://SFTPDownload/$(vFileName)', 0) = 0 then

You will need to substring out the filename from the full path to get this, and note that the library name is required for the FileSize function, but the download requires the local path.

Obviously, if files can change on the SFTP server then this approach will not work, you could then get into checking the file date on the FTP server vs. the local file (taking into account locales and timezones).

Once you have all the files locally it's a standard incremental load from flat files. I would probably create a QVD copy of each source file, and as with the download only recreated QVDs that don't already exist. You can then stitch these together afterwards.

This blog post gives quite a bit of detail on this approach:

https://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

Note that the post is for QlikView rather than Sense, but all you need to change is from local file paths (..\..\etc.) to lib://LibraryName/etc.

Hope that helps,
Steve

Re: Qliksense web connector [SFTP] failed

Bhagwat09 — Thu, 31 Mar 2022 09:18:33 GMT

Hello @Andre_Sostizzo

I am fetching the data(.csv files) from SFTP on regular interval. [like for last 6 month]But now suddenly my reload fails frequently. So I am trying to make new connection but my QWC is not allows me to open .

The code

FilesAndFolders:
LOAD
Name,
Path,
IsDirectory,
CreationTime,
LastAccessTime,
LastWriteTime,
Size
FROM [lib://sftp_url (allenlund_nprinting)]
(URL is [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=harmony-la.allenlund.com&port=5022&userName=QlikServer&password=sta_gen_1%3aekhHROY5hOEHHqLj%2fHgFaQ%3d%3d%3aQ5a0YHe5UOEN%2fkNHJBVKuw%3d%3d&ignoreProxy=False&ignoreClientCertificateErrors=False&useLegacyGroupExchange=False&remoteDirectory=%2fhome%2fQlikServer%2foutbox%2f*.csv*&appID=],qvx);
LET vNoOfFiles = NoOfRows('FilesAndFolders');

Please help me

Thanks

Bhagwat K

Re: Qliksense web connector [SFTP] failed

stevedark — Thu, 31 Mar 2022 11:43:52 GMT

Hi @Bhagwat09

In order to run the Qlik Web Connectors Desktop app you will first need to stop the QWC Service under Services in Windows.

However... it is most likely that you don't want to run the Desktop app, rather open a browser on the server and go to http://localhost:5555/ and you will find the config screens there.

The best way to debug a QWC problem is to copy the full URL into a web browser on the server. This will then either give you an XML success message, or a page of information giving error details. You can F5 in the browser to keep retrying a chunk of code, until it fails and gives a message.

Note that you really need to edit your message above, as although you have removed some of the detail of the URL from what is shown in the message the full URL is still there on the link.

Hope that helps.

Steve