CODEWITHSUNDEEP
excelssismetadatavisual-studio-2019powerquery
Olivier
Olivier

Reputation: 1

SSIS - Power Query Source: setting connection at runtime

I'm trying to use the Power Query source component in a generic way from SSIS (VS2019). The idea would be to use a for each loop to load and transform Excel files. At run time, I need to set the connection manager properties for each file as well as the PQY script to be executed on the file. What I did so far is trying to create a JSON connection string inside a script component and assign the connection string to the connection manager. It keeps on saying that the file requires credentials. Would someone already experienced that kind of dev? All the files do have the same structure so far, do meta-data need to be refreshed too?

[Edit] 1. In the control flow, I'm retrieving the PQY script I want to apply from a DB. Before transormations, script starts like this:

let Source = Excel.Workbook(File.Contents("path_to_a_file.xlsx"),null,true),RawData_Sheet = Source{[Item="Table1",Kind="Table"]}[Data]..."

  1. In the C# script task, I'm replacing the path to excel file by the current file variable. M Script is stored in a variable used in the PQY component. C# Script is then updating the PQY connection manager to target the appropriate file: ConnectionManager _conn = Dts.Connections["Power Query Connection Manager"]; String _ConnectString = "[{kind:File,path:path_to_a_file.xlss,AuthenticationKind:Windows,Username:myusername,Password:mypassword}]"; _conn.ConnectionString = _ConnectString;

  2. The PQY component is left has it is, connected to ["Power Query Connection Manager"] and getting its script from the variable I set. PQY configuration screen

Thanks for any tip on this,

Olivier

Upvotes: 0

Views: 384

Answers (1)

billinkc
billinkc

Reputation: 61211

I can't address the specifics of the PQ but generic anything in a Data Flow will not work.

The Data Flow task works because it makes a strict contract between the source(s) and the destination(s). These columns with these data types will be in play during the run. It's a design-time contract because that allows the run-time engine to allocate resources based on how many buffers of data the system can support. Each row is X bytes, we have Y bytes of memory available, so Z buffers worth of data plus parallelism stuff.

Wish I had a better story to tell you.

Upvotes: 0

Related Questions