Extending timeout setting for Reference Data Services (RDS)

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

DQS, by default, uses connection timeout of 1 minutes when connecting to external Reference Data Services (RDS) provider.  You can modify the timeout setting, using the following steps:

• Run the following query on SQL Server where DQS is installed:

SELECT [ID]

,[SERVICE]

,[NAME]

,[UPDATE_TIME]

,[DESCRIPTION]

,[VALUE]

,[REVISION]

FROM [DQS_MAIN].[dbo].[A_CONFIGURATION]

where id=916

• Click on the value cell (XML) and copy the XML output

• Change the ConnectionTimeout parameter (default 60000 or 1 minute) and update the record ( Tips : Make sure to update DQS_MAIN database before you make the update), for example:

BEFORE

update [DQS_MAIN].[dbo].[A_CONFIGURATION]

set value = '<configuration name="ReferenceData">

<param name="CacheEnabled" value="1" />

<param name="CacheCleanupIntervalMinutes" value="1440" />

<param name="CacheInvalidationTimeHours" value="24" />

<param name="CacheSizeEntries" value="1000000" />

<param name="BatchQuerySizeEntries" value="100" />

<param name="QueryMinConfidence" value="0.4" />

<param name="QueryMaxSuggestions" value="5" />

<param name="ProxyAddress" value="" />

<param name="ConnectionTimeout" value="60000" />

<param name="DataMarketCatalogEntryPoint" value="services/my/Datasets?$filter=Category%20eq%20''Data Quality Services''" />

<param name="MetadataEntryPoint" value="/$metadata" />

<param name="RetryErrorCodes" value="504" />

<param name="RetryReduceFactor" value="5" />

</configuration>'

where id=916

AFTER

update [DQS_MAIN].[dbo].[A_CONFIGURATION]

set value = '<configuration name="ReferenceData">

<param name="CacheEnabled" value="1" />

<param name="CacheCleanupIntervalMinutes" value="1440" />

<param name="CacheInvalidationTimeHours" value="24" />

<param name="CacheSizeEntries" value="1000000" />

<param name="BatchQuerySizeEntries" value="100" />

<param name="QueryMinConfidence" value="0.4" />

<param name="QueryMaxSuggestions" value="5" />

<param name="ProxyAddress" value="" />

<param name="ConnectionTimeout" value="300000" />

<param name="DataMarketCatalogEntryPoint" value="services/my/Datasets?$filter=Category%20eq%20''Data Quality Services''" />

<param name="MetadataEntryPoint" value="/$metadata" />

<param name="RetryErrorCodes" value="504" />

<param name="RetryReduceFactor" value="5" />

</configuration>'

where id=916

Note: make sure to qualify single quote(‘) for DatamarketCatalogEntryPoint with two single quote (‘’)

• Restart sql server