Importing PowerQuery results to SQL Server

[madpiet]

Hmm... have I asked this question here before? LOL



My source data is in tables in PDFs, which I can use PowerQuery to read/parse/clean. That bit works fine. What I would really like to do is use a for each file loop in maybe SSIS and then inside the loop, read the "next" file, parse, and write the results to my SQL Server table. and trap for errors and move the processed files to a final folder. All of that would be trivial in SSIS, except that the PowerQuery source in SSIS doesn't seem to work properly. (Still not certified... I mean, it's only 2+ years old!)

Is there a way to do this? If I execute the PowerQuery from Excel, then I'd have to force the Excel file to requery to cause the PDF files to be re-read. How do I do that in SSIS? Then I can write the data somewhere... but maybe I did it wrong, because SSIS seems to ignore the data types in PowerQuery. Do I need a data type conversion step in there?

There's an add-in that lets you write your PowerQuery results to SQL Server, but it's a black box - you can't do anything with the "intermediate" results (so I wouldn't be able to remove something like redundant records before inserting, for example).

Is there a way to basically output the results into SQL server easily? I've seen a few crazy videos doing it, but the way they do it is nuts (like writing a weird query that will make PowerQuery do writeback).

[NoellaG]

If you save the results of the power query into numbered Excel files (I used to add the date + time to the filename) in a fixed folder, you can let a SSIS package loop through the Excel files in this folder, import them and after a successful import write/move the file in a folder 'Imported', on error move the file to a folder 'ImportErrors'. That would seem the most simple method to me.

[madpiet]

If you save the results of the power query into numbered Excel files (I used to add the date + time to the filename) in a fixed folder, you can let a SSIS package loop through the Excel files in this folder, import them and after a successful import write/move the file in a folder 'Imported', on error move the file to a folder 'ImportErrors'. That would seem the most simple method to me.

How would you force the Excel files to requery the folder of PDF files from within SSIS? I already made it work in Excel pretty much like that.

[NoellaG]

You can create a loop task. I'll check my old documentation this weekend that shows how I did it. If I find it I will post it, but now I have to make ready to go to a party :-) .

[madpiet]

I could do it from something other than SSIS (like Excel or Access using automation... instantiate Excel then call whatever it is to RefreshAll or whatever) But that's just automating Excel. I might just do that and then push the whole thing through Access into SQL Server. A bit of a brute force method, but the PowerQuery source in SSIS never worked for me. (I can't map the columns from the output of the transform to my table... none of the field names show up. Maybe I'm missing a step?)

[NoellaG]

Hi, so if you have 6 PDF files, I assume you have created 6 Excel files in the export folder. Next step is to create a SSIS package that loops through the export folder and imports the data from the file in the SQL server table.
The included document describes how to configure this package. It is an excerpt of an import doc I have written where I have deleted all sensitive information. Be aware that the project dates from some years ago.

[madpiet]

Thanks! I'll have a look and see if it helps me sort out where I'm going wrong.

[madpiet]

Noella,

Any idea how to force each of the Excel files to re-execute the PowerQuery before doing the import in SSIS? I know I could automate Excel... but definitely not ideal. (Well, unless I can get the PowerQuery source in SSIS to work... which apparently doesn't really work).

I think I have the rest of the problems sorted out.

Thanks,
Pieter

[NoellaG]

Write a powershell script to do this and call the script from SSIS https://www.mssqltips.com/sqlservert...-ssis-package/

[madpiet]

Oh, okay! Thanks! I'll work on it.

[madpiet]

I think I figured it out... well, one way. I created two linked tables: one to the Excel file that runs the PowerQuery, and another to SQL Server table that the data will ultimately be appended to. Then I set the Refresh On Load property of the Excel file to True for the "template"... (so it refreshes automatically). Then I just re-point the Excel link to a new file ... doing something like a for each file loop (DIR()) and then for each file returned (in an array, I think)... just loop over that, reset the connection property to the Excel table, then run the append.

The single file test works... now to test with the for each file loop.