Archive for July 18, 2017

avoid time-out for Denodo ADO Net Source in SSIS

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”


Performance Tuning in SSIS for Denodo ADO Net Source

Assumption: You have Denodo driver installed on server. (I had used “DenodoODBC Unicode” version, 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.

I hope this helps.

%d bloggers like this: