Ssis-905 — 4k

| # | Requirement | Acceptance Criteria | |---|-------------|----------------------| | FR‑1 | Detect 4K video files | Any file with width ≥ 3840 px AND height ≥ 2160 px is flagged as 4K. | | FR‑2 | Extract required metadata | FileName, FilePath, FileSize, Width, Height, FrameRate, BitRate, Duration, Codec, Container, SHA‑256 hash. | | FR‑3 | Validate business rules | • BitRate must be between 25 Mbps and 120 Mbps.
• FrameRate ≤ 60 fps.
• Container must be MP4, MOV, or MKV. | | FR‑4 | Persist metadata | Successful rows are up‑serted into dbo.AssetCatalog. | | FR‑5 | Audit & alert failures | All validation failures are recorded in dbo.ValidationLog and trigger an email to mediaops@mycompany.com. | | FR‑6 | Idempotent processing | Re‑running the package on the same file does not create duplicate rows. | | FR‑7 | Performance | Process at least 30 GB/hour on a 4‑core VM (≈ 10 files/min for 2‑GB 4K assets). | | FR‑8 | Logging & telemetry | All steps write to the built‑in SSIS logging provider (SQL Server) and optionally to Azure Application Insights. |

-- AssetCatalog (existing, add columns)
ALTER TABLE dbo.AssetCatalog
ADD
    WidthInt          INT               NULL,
    HeightInt         INT               NULL,
    FrameRateDecimal  DECIMAL(5,2)     NULL,
    BitRateInt        BIGINT            NULL,
    DurationSec       INT               NULL,
    CodecNVarchar     NVARCHAR(50)      NULL,
    ContainerNVarchar NVARCHAR(10)      NULL,
    SHA256Hash        CHAR(64)          NULL,
    Is4K              BIT               NULL;
-- Staging queue (new)
CREATE TABLE dbo.VideoFileQueue (
    QueueID          BIGINT IDENTITY(1,1) PRIMARY KEY,
    FilePath         NVARCHAR(400) NOT NULL,
    FileName         NVARCHAR(260) NOT NULL,
    DetectedDT       DATETIME2      NOT NULL DEFAULT SYSDATETIME(),
    ProcessedDT      DATETIME2      NULL,
    StatusCode       CHAR(1)        NOT NULL DEFAULT 'N' -- N:new, P:processing, S:success, F:failed
);
-- ValidationLog (new)
CREATE TABLE dbo.ValidationLog (
    LogID            BIGINT IDENTITY(1,1) PRIMARY KEY,
    QueueID          BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.VideoFileQueue(QueueID),
    ValidationMsg    NVARCHAR(4000),
    IsError          BIT NOT NULL,
    LoggedDT         DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

Accessing and viewing content like SSIS-905 4K usually requires specific hardware and software capabilities. For instance: SSIS-905 4K