Drew,

yes, I did. Sorry, just been doing other stuff.  I'll go look at it and add questions to that one. Because I know I'm going to be confused.

Sorry about that!

Thanks,

Pieter

Where is your question? Are you trying to determine if ProcessDate is between StartDate and EndDate?

look up windowing functions. new since 2012 at the latest.

if it's only one or two values, then I probably wouldn't use a lookup table. It would be simpler to just use a CASE statement, but if there are lots...

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 2 years, 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 2 years, 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 2 years, 4 months ago by pietlinden.