Not sure how many people are using Integration Services with Snowflake. I have setup the Connection and I’m able to run a query and get results from Snowflake, however the fields are returned in a random order and not as per the query.
I have also identified a field that doesn’t get returned in the results. This also occurs if I do Select * instead of selecting individual fields. The only thing I’ve noticed is that the column contains all NULL values, but thought the column should be returned and not excluded from the results.
Hi @craig.norton, could you please let us know the version of the activity pack you are using? Additionally, sharing the Studio version and the query would be very helpful.
Just to confirm my understanding, are you saying that the same query returns values for the column when executed directly in Snowflake?
That is correct. When running the query directly in Snowflake it returns the column and all values were NULL. When it is run via Studio that column doesn’t appear in the results data set. I tweaked the underlying data yesterday and today’s extract now has the column appearing because one of the records has a value.
Its weird that the column isn’t appearing, and my other comment how the order of the results doesn’t match the order of the query is also weird.
This is the query that I ran:
“select nr.PORTFOLIO_CODE, nr.”“Portfolio Name”“, nr.”“Tax Non Res Country”“, nr.”“Tax Non Res Country Description”" , nr.““Primary Contact Person””, nr.SECURITY_CODE, nr.““Asset Class Category””, nr.““Registry Description””, nr.““Case Type”” from EDW_DM_WEALTHPLUS_PRD.PRD_RPT.““rpt_wealthplus_dm_non_residents_ad_hoc_change_of_residency_all_new_changed_rows”” nr"
And this is the order that came out in the results.
The Snowflake integration activities are not versatile when running queries.
For each query you want to run, you need to put the query in the activity with no variables. Then, you click “Generate output schema.” This tells the activity what columns you expect to be returned each time the activity is run. At this point, you can put in some variables if you want, but you need to be mindful of what you are substituting.
If you try to put a different query in the activity that returns columns that are not in your output schema, the activity won’t work. If you put in a query that returns only a subset of the columns in your output schema, the other columns will still be returned only with all NULL values, as you observed.
This need to “Generate output schema” really limits what the activity is good for. It makes it very difficult to use with Agents because you want a Agent to be able to dynamically generate queries to run with the activity, but you can’t.
I have done a lot of digging, and I have not found a way to run bespoke Snowflake queries through a Studio activity. If anyone has an answer,please let me know because it would unlock a lot of use cases!
i been done dealing with the same over the last week and ended up just using snowflake documentation for the .net client / snowflake.data nuget and it at least it works more reliably and dynamically - not pretty but i’ll compile a new activity and throw it in marketplace maybe after i finish my work lol
@kckustomac1 You managed to query Snowflake synchronously on your own using their documentation? Mind sharing how you established the connection to use in Studio? Just trying to reduce the time to getting an answer. It’s taking about 2 minutes right now, which is pretty long.
Old topic, but rather than start a new one I’ll piggyback here.
I’m trying to use this activity in a UiPath Studio App and am having trouble getting the output to display. I’ve been able to do this operation in Studio Desktop, for an RPA process, but I can’t get it working in the App due to issues with binding the results of the query.
As you know, the “Execute Select Query Synchronously” activity returns a “Query results” object, which is a complex custom UiPath object. In Studio Desktop I assign this to a variable called QueryResult, which gives QueryResult the type “executeQuery_List[]”, then iterate through that to get values via index.USERID, index.ROLEID, etc. I build my own DataTable and add the values as DataRows in the loop.
In the Apps designer it’s … different. The “Query results” object returned here contains a similar object called currentexecuteQuery_List of type “executeQuery_List” (no array decoration). The designer defaults to using its own Index variable, which is another symbol like “Query results” that can’t be subscripted. I can find no way to get to the actual values contained and no indication of how to access them.
Ideally I could find the right property on this object and use .ToListSource() for use directly as the DataSource for my table. Short of that I’ll take the RPA approach and build my own table, but I can only do that if I can address the objects in the result set.
Does anyone have any suggestions for making this work? Again, just a Snowflake query → result set > input to DataTable in a UiPath Studio App. Either directly through the result set or after processing that into a ListSource of some type, as that’s what the DataTable object expects.
Finally, in Studio Apps, how is this workflow supposed to present it’s output as the input to the control? The DataSource on the control is “workflow”. My workflow has an “out” arg, but there’s no mention of that in the docs.
