Unpivot in SSIS

Question

Post reply

Unpivot in SSIS

yrstruly

Hall of Fame

Points: 3440

March 14, 2024 at 7:36 am

#4395078

I want to Unpivot some data. Please guide me in selecting the correct columns etc.

My table is:

p01

My Source display:

What I see in my Unpivot View:

Error I am getting:

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at DATA FLOW FROM CSV TO IMETA DATABASE FOR CONSUMPTION [SSIS.Pipeline]: Unpivot.Inputs[Unpivot Input].Columns[P01] has lineage ID 26 that was not previously used in the Data Flow task.

Error at DATA FLOW FROM CSV TO IMETA DATABASE FOR CONSUMPTION [SSIS.Pipeline]: "Unpivot" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Error at DATA FLOW FROM CSV TO IMETA DATABASE FOR CONSUMPTION [SSIS.Pipeline]: One or more component failed validation.

Error at DATA FLOW FROM CSV TO IMETA DATABASE FOR CONSUMPTION: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

More context on my ETL(SSIS) can be found in code:

USE [IMETA_FINAL]
GO

/****** Object: StoredProcedure [Prod].[IMETA - Provisional Revenue SSA Elimination Adjustment_PROC_Other] Script Date: 3/13/2024 5:54:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [Prod].[IMETA - Provisional Revenue SSA Elimination Adjustment_PROC_Other]
AS
BEGIN
    INSERT INTO [PROD].[IMETA - Provisional Revenue SSA Elimination Adjustment_Table_Other]
    SELECT
        'FY' + RIGHT(LEFT([Date], 4), 2) AS [Fiscal year],
        RIGHT([Date], 3) AS [Fiscal period],
        CASE
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to Chep' THEN 'E1273T'
            WHEN [Intercompany Revenue] LIKE 'Braecroft to Weatherboard ' THEN 'E1272T'
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to CHEP SSA - Repairs' THEN 'E1273T'
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to CHEP SSA - Pallets' THEN 'E1272T'
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to CHEP India - Repairs' THEN 'Exclude'
            WHEN [Intercompany Revenue] LIKE 'INTERCO-SALES SSA - EXCL INDIA' THEN 'E12740'
        END AS [Entity],
        [Intercompany Revenue],
        [PROD].[IMETA - Removes non-numerical data_Func_Other]([Metric Value]) AS [Metric Value],
        CONVERT(DATE, [Loaddate]) AS [Loaddate],
        CONVERT(DATE, [Month]) AS [Month]
    FROM [Stag].[IMETA - Provisional Revenue SSA Elimination Adjustment_Table_Other] AS SSA_ADJ WITH (NOLOCK)
    WHERE
        (CASE
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to Chep' THEN 'E1273T'
            WHEN [Intercompany Revenue] LIKE 'Braecroft to Weatherboard ' THEN 'E1272T'
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to CHEP SSA - Repairs' THEN 'E1273T'
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to CHEP SSA - Pallets' THEN 'E1272T'
            WHEN [Intercompany Revenue] LIKE 'Weatherboard to CHEP India - Repairs' THEN 'Exclude'
            WHEN [Intercompany Revenue] LIKE 'INTERCO-SALES SSA - EXCL INDIA' THEN 'E12740'
        END) != 'Exclude'
END
GO

Attachments:

You must be logged in to view attached files.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16017 More actions · Answer 1

you have already been given some answers on your original post - https://www.sqlservercentral.com/forums/topic/eliminating-csv-conversion-step

with regards to the errors, they are standard SSIS errors, which you can both read manuals and google for how to fix - metadata errors means you changed your package or source tables without updating package- so go do that.

and if you don't know how to use SSIS for unpivot, load data to SQL and do it in SQL instead.