topic Scheduling a job only full load in Qlik Replicate

Scheduling a job only full load

chandraprakash_j_volvo — Tue, 27 Feb 2024 07:15:20 GMT

Source: Azure SQL database

Target: Databricks Lakehouse(delta)

We wanted to use only the full load and schedule the job every day. Every time we wanted to replicate the record which was added new in between two scheduled runs.

Eg: The job is scheduled at 1 PM, we have 100 records from the source & 100 records has been replicated to the target. 20 new records have been created from the source side in between the 2 scheduled runs. I just want to replicate only 20 records to the target side on the next scheduled run, not 120 records.

Note: Considering cost optimization CDC is not enabled from the source side (SQL database).

Is there an option do to this?

Re: Scheduling a job only full load

SachinB — Tue, 27 Feb 2024 07:31:24 GMT

Hello @chandraprakash_j_volvo ,

Thanks for contacting Qlik community

We would suggest writing a script to create a view. Before full load starts, re-create a view that contains new records only and let Replicate loads data from this view.

Hope this helps you.

Regards,

Sachin B

Re: Scheduling a job only full load

aarun_arasu — Tue, 27 Feb 2024 07:34:31 GMT

Hello @chandraprakash_j_volvo ,

Thank you for reaching out to the Qlik community.

Qlik Replicate does not support incremental load, and if CDC is not an option for you, we recommend considering the use of passthru filter. This allows you to define a condition to instruct Replicate on what data to load.

For more information and a reference article on using passthru filter to filter for the last 90 days of data, please visit: Filter for last 90 days of data in Qlik Replicate

We hope this helps! If you have further questions or need assistance, please don't hesitate to ask.

Regards,

Arun

Re: Scheduling a job only full load

chandraprakash_j_volvo — Wed, 28 Feb 2024 06:26:34 GMT

Hello,

Creating Metadata Manager's utility components failed
Cannot create the source utility component
Failed while preparing stream component 'Databases'.
Failed to connect
RetCode: SQL_ERROR SqlState: CE269 NativeError: 0 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error 12002 sending request to https://login.windows.net.

From the source side, we have an access.

We have db_datareader for the user through which we connect to the source endpoint. And as mentioned in the Qlik Pre-requistes:

grant VIEW DATABASE STATE
grant SELECT .

But still we face the issue

Re: Scheduling a job only full load

aarun_arasu — Wed, 28 Feb 2024 07:18:23 GMT

Hi @chandraprakash_j_volvo ,

Thanks for reaching out to Qlik Community.

I could see you have opened as support case for same issue and from the logs , below is what we noticed

RetCode: SQL_ERROR SqlState: 08S01 NativeError: 10054 Message: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host. [1022502] (ar_odbc_conn.c:584)

This normally happens due to following reasons

Possible Causes:
Network Interruption: The most common cause is a network interruption or instability.

Firewall or Security Software: Firewalls or security software may be blocking the connection or closing it due to perceived security risks.

Server Timeout Settings: The SQL Server might be configured with a low timeout setting, causing it to terminate long-running connections.

Below are some reference article to troubleshoot

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-SQL-Server-idle-connections-force-closed/ta-p/1945239

https://stackoverflow.com/questions/2582036/an-existing-connection-was-forcibly-closed-by-the-remote-host

Please collaborate with network team for more insight

Regards

Arun