Replace OLEDB source editor task with a powershell script

  • 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?

     

    Screenshot 2025-01-16 at 19.57.51

    sample query used in the source(existing transform)

    Screenshot 2025-01-16 at 20.00.08

  • 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?

  • 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 * FROM bigquery-public-data.san_francisco_trees.street_trees'

     

  • jaango123 wrote:

    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.

    • This reply was modified 1 week, 6 days ago by  Phil Parkin.

    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

  • 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

  • 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

     

  • 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