Search code examples
ssissnowflake-cloud-data-platformtimeoutodbc

SSIS Import/Export Wizard ODBC/Snowflake timeout

We are using SQL Server 2014 (running the 12.6444 latest Service Pack/CU), and trying to import data using the SSIS import/export wizard from Snowflake, using an ODBC connection, using the latest ODBC drivers from snowflake.

It generally successfuly connects and imports data on small(er) queries. We're running a query that takes a few minutes to run, but keeps timing out after 30 seconds, followed by this error:

- Pre-execute (Error)
Messages
Error 0xc0047062: Data Flow Task 1: System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] [Snowflake][Snowflake] (15) 
  Query has timed out. User specified timeout in seconds=30, Elapsed=45

We've already established timeout properties in the data source windows, but this didn't change anything:

Dsn=snowflake;query_timeout=0;commandtimeout=0;login_timeout=0;statement_timeout_in_seconds=4000;timeout=0

enter image description here

Anything else we should consider?

Solution

  • I got this resolved.

    I ended up saving the DTSX package generated by the import/export wizard, and opening it up Visual Studio – And after navigating through a bunch of settings, there is a CommandTimeout option which I manually set to “0”. (This option isn’t configurable during the SQL import/export wizard).

    This worked and the data imported successfully.

    enter image description here