January 16, 2025 at 8:06 pm
I want to replace all the oledb source editors as shown below with powershell script tasks that execute sql queries. Please give me some inputs on how to construct the powershell script so that it will a replacement for all the oledb source editor task
so the powershell script could be something like below instead of the ole db source sql query? How do i construct the query and save the results in the powershell so that it goes to the union transformation task?
$Query = "SELECT MY_TEXT from VW_DEMO_Organization"
Output goes to union?
sample query used in the source(existing transform)
January 16, 2025 at 10:48 pm
Not sure I understand why you would want to do that...can you elaborate on the use case? What problem are you trying to solve?
January 17, 2025 at 8:51 am
Hi Martin,
please see this - https://www.sqlservercentral.com/forums/topic/how-create-a-google-bigquery-connection-manager-in-ssis
I am trying to use a google bigquery connection. However It seems not so straight forward as I cant seem to find the connection as mentioned in the above post.
However i can use a powershell script easily (by defining the bigquery client within the script as below.
$project = "your-gcp-project"
$accesstoken = gcloud auth print-access-token # Lazy way. Can also get access token using service account and 2LO - ref.
$query = 'SELECT * FROMbigquery-public-data.san_francisco_trees.street_trees
'
January 17, 2025 at 9:08 am
Hi Martin,
please see this - https://www.sqlservercentral.com/forums/topic/how-create-a-google-bigquery-connection-manager-in-ssis
I am trying to use a google bigquery connection. However It seems not so straight forward as I cant seem to find the connection as mentioned in the above post.
Are you using a 32-bit version of VS? If so, you might need to create a 32-bit DSN. Not something I've done, but worth a shot.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 17, 2025 at 11:53 am
original question - NO - you can not have the output of powershell as a source on your dataflow - you could however do the same in C# component source.
Regarding your other post and what Phil mentioned above - that is likely your issue - but you should also look (and I strongly advise you do it) at DSNLess connections - you may be able to build the required connection without the need to have a DSN Setup on your system
January 17, 2025 at 2:30 pm
Thanks the 32 bit resolved the DSN issue.
Can you give some hints/clues o dsnless connections in bigquery?
C# component. ------> If i use this will it be dsnless? I guess so as in the code we can build the bigquery client. If i use a c sharp component task and use the sql query, can i feed to a Union transformation task? I will try this
January 17, 2025 at 2:34 pm
look at https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting-data-flow-script-component-types/creating-a-source-with-the-script-component?view=sql-server-ver16 - this can feed to any dataflow component that accepts input (union is one of them)
January 17, 2025 at 4:40 pm
Thanks.
In the c sharp code we need to install a Bigquery client library so that it can be used in the code. Is this installation even possible with component source?
dotnet add package Google.Cloud.BigQuery.V2
using System;
using Google.Cloud.BigQuery.V2;
namespace BigQueryDemo
{
class Program
I will go through the link
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy