Importing PowerQuery results to SQL Server

[madpiet]

I have a PowerQuery in Excel that basically reads and cleans up a PDF of what looks like a Reporting Services report which contains a table. My PowerQuery sorts all that mess out and changes all the data types. So, in theory, I should be able to import that into SQL Server pretty easily.

The PowerQuery source in SSIS is complete crap. Just flat out doesn't work at all, so I can't just move the PowerQuery to SSIS and then append that way.

Is there any way to keep the data types in PowerQuery when importing to SQL Server? (Can I do that with BULK INSERT and a format file so that the data types are cast before the insert occurs?)

IS there any other way of doing this with a local SQL Server (not Azure)... I know Azure can create a pipeline to do the transforms and then dump the result into a database... Kinda like should have been done in PQ on the desktop, but then MSFT couldn't charge for it. =(

Thanks!

[NoellaG]

If you have the results in Excel, why not use SSIS to import the Excel sheet?

[madpiet]

Because I was hoping I could loop over a collection of PDF files, which is where the data is coming from. Instead I have to use Excel to get all the PDFs from a folder, parse and import that, and then use SSIS to import from Excel. Because the PowerQuery source doesn't really work in SSIS. (Makes me wonder if that was on purpose!)

SSIS blows. I guess that's MSFT's way to force people to pay for Azure? Sheesh.

[NoellaG]

Haven't used it myself, but you could try R to import the files: https://www.mssqltips.com/sqlservert...to-sql-server/

[madpiet]

Haven't used it myself, but you could try R to import the files: https://www.mssqltips.com/sqlservert...to-sql-server/

Or I guess I could create linked tables in Access and use that to do the insert... and use DIR() to loop over a set of files...

[madpiet]

Silly me! In PowerBI, make sure you have some external tools installed. I have DAX Studio 3.0.11 installed as an add-in. So it can see my model. From there I can go to External Tools > DAX Studio > Advanced Tab > Export Data > SQL Tables... specify database etc and you can export all the data to SQL Server.

So be sensible and get all the data into your model and stuff it into SQL Server from there. Let the tools do all the dirty work.