thanks Martin. I'll probably just get a used copy. If I don't like it, I'm only out like $10.

Where's your question?

Are you asking if there's a standard format for "AML" files? Are they XML? Is there an example of one somewhere?

Martin,

have you read Joe Caserta's book on ETL (from the Kimball collection)? ISBN: 978-0764567575

I'm just trying to get my head around doing the ETL etc and building proper dimensional models...

Okay, I got the "explode Starter ingredients" bit working, so now it's getting easier!

CREATE VIEW [dbo].[vwBaseIngredientRecipe]
AS
WITH cteRecipe(RecipeID, RecipeName, IngredientID, IngredientName, RecipeWeight)
AS (
SELECT 
r.RecipeID
,r.[Recipe Name]
,i.IngredientID
,i.IngredientName
,ExpandedRI.RecipeIngredientWeight

FROM
/* non-Starter ingredients */
(SELECT...

GENERATE_SERIES (Transact-SQL) - SQL Server | Microsoft Learn

Or of course you could search around on here. I know there are folks here that have built TVFs to generate series...

• This reply was modified 1 year, 2 months ago by pietlinden.

<checking calendar>

Nope, still 2024.

Drat!

Okay, thanks for looking at this. Thought I was just missing something. Maybe I'm not. The PowerQuery source just isn't nearly as functional as I had hoped it would be....

• This reply was modified 1 year, 3 months ago by pietlinden.

I think the PQ Source is a dead end. It's crazy buggy. Either that or I'm doing something incredibly stupid in SSIS, because if I use the PQ Source, SSIS...

Not 100% sure the destination matters at this point. I can't get the PowerQuery Source to output anything at all. If I use an Excel source (that runs a PowerQuery),...

Okay.. I got things working so far... not quite what I want, but closer:

Okay... progress, of a sort.

Excel Source

to

Derived Column (unit price)

to

Data Conversion (Convert PQ and Excel Types to SQL...

I tweaked my code because I wanted to be able to call the PowerQuery function that reads the PDF once for each file... here's the code (Note: this works perfectly...

• This reply was modified 1 year, 4 months ago by pietlinden.

Just wondering... can I put the PowerQuery source inside a For Each File Loop, and then, if I make the PowerQuery a variable, basically do a REPLACE (Yes, I know,...

DECLARE @TextToParse VARCHAR(100) = 'for col1, for col2, for col3, for col4';

INSERT INTO #SomeTable(SplitValue)
SELECT TRIM(value) AS SplitValue
FROM string_split(@TextToParse,',');

is one way

This is absolutely a post which would benefit immensely from a sample record or two.

I see no reason why you can't use something like DelimitedSplit8K or SPLIT_STRING() to separate the...

oh, the 25 minutes. that's the giveaway. use LAG, like I said.

use tempdb;
go

CREATE TABLE ds(
ID INT NOT NULL,
Flag BIT,
EventTime DATETIME2
);
GO
INSERT INTO ds VALUES
(10,0,'2024-06-24 14:20:19.437'),
(11,1,'2024-06-24 14:45:45.027'),
(12,0,'2024-06-24 14:47:35.067'),
(13,1,'2024-06-24 15:14:45.653'),
(14,0,'2024-06-24...