January 9, 2025 at 6:26 pm
I have a powerQuery that reads and transforms a table in a PDF. Works a champ.
If I wanted to write the resulting table's data to SQL Server, I can use DAX Studio (kind of) because it creates a newtable and writes the data to it. But I wanted to just map the columns to an existing table in my database and then make that available to run on a schedule, I'm stuck with SSIS, which doesn't really play nice with PowerQuery.
So how should I do this? Do the PowerQuery in Excel? But then how do I force a refresh/re-execution of the PowerQuery from SSIS (well, automating Excel). VBA?
In theory, over time, I'd collect PDFs from the vendor, and then drop them in a folder and have SSIS read them and dump the results into my database and then move them. I'd do the whole thing in PowerQuery if it let me move files. But PQ is a strange beast... no file handling, no .write methods like Python...
So how would you accomplish this? If it were a "normal" file, I'd just use SSIS, and a For Each file loop and move the processed files. But PowerQuery still isn't certified to work in SSIS.
January 9, 2025 at 11:15 pm
based on all your threads about powerbi and ssis why don't you go read https://www.mssqltips.com/sqlservertip/6333/sql-server-integration-services-power-query-source/ - if it works for some it needs to work for you - you just need to figure out what you doing wrong.
January 11, 2025 at 5:41 pm
I think I found an answer. It's just a total hack, but I'll test it out.
you basically wrap an insert statement in a try catch block and then refer to the individual local (i.e. powerBi) column names in the in SQL statement and convert them all to text using Text.From() and then wrapping that mess in delimiters like you're building a string variable in VBA. Then you execute inside the try/catch block. Then you have the statement run a SELECT statement to force the whole thing to execute.
BEGIN TRY
INSERT INTO MyTable(col1, col2...) VALUES ( ... ) <-- that gets ugly
END TRY
BEGIN CATCH
END CATCH;
SELECT 'done' AS Message;
January 11, 2025 at 11:05 pm
if you have the data available within c# then you are doing something wrong.
even if only c# was available, and you have to do that conversion within C# (which you should not have to do ), issuing insert statements like that is the "incorrect" way of doing it - add to a dataset/datatable and at the end write that contents in one go to SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply