Validate SSIS packages for VS_NEEDSNEWMETADATA

Question

I'd like to automatically check my deployed packages for any metadata issues, such as if a column data length is increased from varchar(20) to varchar(60) before they're run. I encountered an error in the package at midnight and I'm hoping to catch it sooner.

I've tried both [SSISDB].[catalog].[validate_package] and catalog.validate_project but strangely they don't seem to catch this specific error. However, if I open up Visual Studio, the error shows up in the data flow component immediately without running the package.

Are there any methods to validate the package for metadata changes?

Edit: Additonal information. This is an Oracle Source database so I'm using Attunity Oracle Source Connector. I have DelayValidation set to False on the Data Flow Task and ValidateExternalMetadata = True on the Source component.

Answer 1

If you right click on the package and select Validate, behind the scenes, as you've identified, the SSISDB.catalog.validate_package procedure is executed.

It does it thing and records to SSISDB.catalog.operation_messages

What you want to do is look for messages with a message_type of 110 (warning) or 120 (error)

Reproduction

I created the following table.

CREATE TABLE dbo.so_37034528
(
    Col1 int
,   Col2 int
,   Col3 varchar(20) 
,   Col4 bigint
);

And a simple SSIS package that selects all the columns and routes to a row count component.

The biml is

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="tempdb" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
    <Packages>
        <Package Name="so_37034528">
            <Variables>
                <Variable DataType="Int32" Name="RowCount">0</Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="string">
                    <Transformations>
                        <OleDbSource ConnectionName="tempdb" Name="OLESRC GetData">
                            <DirectInput>SELECT * FROM dbo.so_37034528;</DirectInput>
                        </OleDbSource>
                        <RowCount Name="RC Original Rows" VariableName="User.RowCount" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

I deployed that to my server and ran the following query

DECLARE @validation_id bigint;
EXECUTE SSISDB.catalog.validate_package
    @package_name = N'so_37034528.dtsx'
,   @validation_id = @validation_id OUTPUT
,   @folder_name = N'so'
,   @project_name = N'so'
,   @use32bitruntime = False
,   @environment_scope = A
,   @reference_id = NULL;

SELECT
    @validation_id;

-- Wait some finite amount of time for validation

WAITFOR DELAY '00:00:20';


SELECT
    D.message_desc
,   OM.message
FROM
    SSISDB.catalog.validations AS V
    INNER JOIN
        SSISDB.catalog.operation_messages AS OM
        ON OM.operation_id = V.validation_id
    INNER JOIN
    (
        VALUES
            (-1,'Unknown')
        ,   (120,'Error')
        ,   (110,'Warning')
        ,   (70,'Information')
        ,   (10,'Pre-validate')
        ,   (20,'Post-validate')
        ,   (30,'Pre-execute')
        ,   (40,'Post-execute')
        ,   (60,'Progress')
        ,   (50,'StatusChange')
        ,   (100,'QueryCancel')
        ,   (130,'TaskFailed')
        ,   (90,'Diagnostic')
        ,   (200,'Custom')
        ,   (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.')
        ,   (400,'NonDiagnostic')
        ,   (80,'VariableValueChanged')
    ) D (message_type, message_desc)
    ON D.message_type = OM.message_type
WHERE
    V.validation_id = @validation_id
ORDER BY
    V.validation_id;

My results looked something like

Information     The validate operation has started.
Pre-validate    so_37034528:Validation has started.
Pre-validate    string:Validation has started.
Information     string:Information: Validation phase is beginning.
Post-validate   string:Validation is complete.
Post-validate   so_37034528:Validation is complete.
Information     The validate operation has completed.

Seeing that I had no warnings, I repeated the operations as I made changes to the table. First I expanded a column

ALTER TABLE dbo.so_37034528
ALTER COLUMN Col3 varchar(80);

This resulted in a warning

Warning     string:Warning: Truncation may occur due to retrieving data from database column "Col3" with a length of 80 to data flow column "Col3" with a length of 20.
Warning     string:Warning: The external columns for OLESRC GetData are out of synchronization with the data source columns. The external column "Col3" needs to be updated.

I reset my column length and this time dropped a column

ALTER TABLE dbo.so_37034528
ALTER COLUMN Col3 varchar(20);


ALTER TABLE dbo.so_37034528
DROP COLUMN Col4;

Now I have the following output

Warning    string:Warning: The external columns for OLESRC GetData are out of synchronization with the data source columns. The OLESRC GetData.Outputs[Output].ExternalColumns[Col4] needs to be removed from the external columns.
Error      string:Error: "OLESRC GetData" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error      string:Error: One or more component failed validation.
Error      string:Error: There were errors during task validation.