Importing PowerQuery results into SQL Server

MadPiet

Member
Local time
Today, 17:39
Joined
Oct 5, 2010
Messages
34
In theory, it's possible to use the PowerQuery source in SSIS to import PowerQuery results into SQL Server, but so far I haven't gotten it to work. (I wanted to import a single file at a time, because I wanted to be able to handle the failures by moving the file to a "failed" or "imported" directory after the import attempt. When I copied my PowerQuery into SSIS, instead of getting a list of columns in the output, I got no columns.
I'd use Access and Excel to do it, but maybe I need an older version of Access so I can do crazy old fashioned things like looping over directories and calling Excel methods (like Refresh) from within Access. The basic idea of the code is to loop over a directory of PDFs, process them one at a time by reading them with PowerQuery and then importing the results to a permanent table in SQL Server.
If I were to do it with Access, I'd do something like ...
1. specify the import folder path. (Used to be I could use the BrowseFolder API).
2. create a linked table to the SQL Server destination table.
3. loop over each Excel file (use DIR with .xlsx filter...), import the table holding the PowerQuery result to the linked table. (that's easy, but how do I refresh the PowerQuery first so I get the data I'm supposed to?)
4. Log success or failure, move the source file.

is that what I have to do? Or do I just roll back to my old copy of like Access 2013 and do it there, where it actually worked?
 
Has anybody here ever created a PowerQuery that does a hideous transformation and then gotten that to import into SQL Server?
For all the gringos... I wrote a PowerQuery to parse a Costco Purchase History report, and combine the purchases and the discounts so that the discount is in the same record as the purchase, not as a separate record... and I wanted to insert it into SQL Server, but there's no super obvious way to do it. I tried the PowerQuery source in SSIS, but What I really wanted was to do it inside a For Each File loop.
 
cross-posted:
 

Users who are viewing this thread

Back
Top Bottom