18 | July | 2017 | Big Data, Data Analytics and Data Science

avoid time-out for Denodo ADO Net Source in SSIS

July 18, 2017 Umesh M Patil Leave a comment

In SSIS, I was executing sql command in ADO Net Source of one of the Data Flow and was getting Time-out error after approximately 15 minutes. I used below clause after select statement.

context (‘querytimeout’ = ‘9000000’)

Here time is in milliseconds (in our case, ‘9000000’ is approx 2.5 hr).

For more info read “CONTEXT CLAUSE” in Denodo documentation in “Advanced VQL Guide”

Categories: Data Visualization, Denodo, SSIS Tags: Data Visualization, Denodo, ssis

Performance Tuning in SSIS for Denodo ADO Net Source

July 18, 2017 Umesh M Patil Leave a comment

Assumption: You have Denodo driver installed on server. (I had used “DenodoODBC Unicode” version 9.03.04.00, when I created User DSN)

Below steps helped me import two million rows (6 columns with mostly int data) data in less than 4 minutes. Earlier it used to take more than one and half hour.

Here trick is to change User DSN settings. :):):)

Click “Start” and type-in c:\Windows\SysWOW64\odbcad32.exe and hit enter in Search Programs and File to open “ODBC Data Source Administrator”
In “ODBC Data Source Administrator”, in “User DSN” tab select appropriate Data source from “User Data Sources” list. Click “Configure”.
Above step will open “ODBS Driver setup” window, Click “Datasource” button
do below changes in “Advanced Options” window
1. on Page 1
  1. in “Data Type Options”, un-select “Text as LongVarchar”
  2. in Miscellaneous, change “Chunk Size” from 100 to 75000
2. on Page 3
  1. in “The Use of LIBPQ library”, change selection from “unspecified” to ”yes” , click Apply, Click Ok.