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.
