Snowflake ODBC integration with Blue Prism

[JonathanJones]

Has anyone had success with connecting Snowflake ODBC with Blue Prism to execute SQL queries against Snowflake warehouses?   If so, were custom VBO's built to achieve this?

------------------------------
Jonathan Jones
Manager
Chesapeake Energy
America/Chicago
------------------------------

[IshanMahajan · Accepted solution]

BluePrism has a ODBC VBO, you need to configure the DB connection in Window's ODBC data source and utilize the connection name(user DSN/system DSN) in Blueprism ODBC VBO to connect to the database.

this VBO is not available in installation folder or Digital exhange but you can send one mail to Blue Prism support team, i believe they can provide that to you.

Or you can utilize a code stage to do that, here is the link 
https://valeriovalrosso.blogspot.com/2018/06/make-your-life-little-easier-with-odbc.html


------------------------------
Ishan Mahajan
India
------------------------------

[JonathanJones]

Thank you!  We'll give that a try.

------------------------------
Jonathan Jones
Manager
Chesapeake Energy
America/Chicago
------------------------------

[JonathanJones]

Thank you again for mentioning the code.  It works just fine.  The only tricky part was the particular connection string for Snowflake and ODBC driver installation.  Below is an example with Snowflake connection string referencing the Driver, Server, Uid, Pwd, and Schema . The query and network timeouts parameters are optional...

string connectionString = "driver=" + driver + ";" +
  "uid=" + uid + ";" + 
  "pwd=" + pwd + ";" + 
  "authenticator=" + authenticator + ";" + 
  "server=" + server + ";" + 
  "schema=" + schema + "; +
  "query_timeout=60;+network_timeout=60;";
OdbcConnection connection = new OdbcConnection(connectionString);
errorMessage = "";
result = new DataTable();
success = false;
try 
 { 
  connection.Open();
  OdbcCommand command = new OdbcCommand(sqlCommand, connection);
  OdbcDataReader dataReader = command.ExecuteReader();
  result.Load(dataReader);
  success = true;
 }
catch (Exception ex)
 {
  errorMessage = ex.Message;
 }
finally
 {
  connection.Close();
 }

------------------------------
Jonathan Jones
Manager
Chesapeake Energy
America/Chicago
------------------------------

[IshanMahajan]

Glad that it worked.

------------------------------
Ishan Mahajan
India
------------------------------

[SohanaKamat]

Hi ,

I am using the ODBC object provided by the blueprism support team but my queries are getting ti,ed out. I added CommandTimeout in the code and still face the same issue.
When i try the same query on the HUE Console, the response comes within 60 seconds. Is this something you had come across when implementing your solution with the ODBC object.
 @ Jonathan Jones @Ishan Mahajan I would be grateful if you can help me out with the same.

Best regards,
Sohana

​

------------------------------
Sohana Kamat
RPA Consultant
L&T Infotech
------------------------------

[IshanMahajan]

Hi,

We also faced timeout issue but the DBA team helped to performance tune SQL so it runs very fast now. 

I have not looked into the ODBC driver VBO code, not sure if that is similar to the code link i i shared on above thread, can you try that if it is different ?

------------------------------
Ishan Mahajan
India
------------------------------

[SohanaKamat]

Hi,

I am using the object provided by blueprism support team. I have fine tuned my query with the DBA team but the error of Query Execution timeout occurs sometimes and other times it runs smoothly. The code is the same, the only difference is I have added a commandtimeout to check if that would help with the error.
Please let me know if you have any suggestions regarding the same.

------------------------------
Sohana Kamat
RPA Consultant
L&T Infotech
------------------------------

[Sathish.Rangan]

Hi,

Anyone succeeded with snowflake connection with blue prism? I downloaded the VBO and installed snowflake driver but getting error as "

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

In the VBO pre-requsities its been mentioned that 32 bit driver is required, but we are using 64bit VM. Will it be an issue? Please suggest.

If yes, what could be the alternate way to connect snowflake with blueprim.

Please help.

Thanks,
Sathish R

------------------------------
Sathish Rangan RPA Tech Lead
RPA Developer
TATA Consultancy services Limited
------------------------------

[ksandesh97]

Hi Satish,

I recently worked on integrating Snowflake with Blueprism. I have seen 32-bit Snowflake ODBC driver from officail site will work with Blueprism 7 and up. 64-bit doesnt work with Blueprism even though the system is 64-bit.

Regarding DSN or default DSN, please supply the DSN name to Connection string given during installation and setup. This will resolve the error you are getting.

------------------------------
Sandesh K
Consultant/RPA Developer
Deloitte USI
Karnataka, India
------------------------------