There are about 100 packages deployed to SSIS server. All the packages are the same. All of them had worked for a long time. However, on one certain day all of them started failing with the same error:
"Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'SELECT TOP 1 1 [TableName] FROM ( SELECT TOP 1 1 [Ta' is not compatible with the statement 'SELECT TOP 1 1 [TableName] FROM sys.[tables] WHERE 1 = 0'. End Error Error: 2019-11-15 12:39:20.40 Code: 0xC020204A
Source: Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. End Error Error: 2019-11-15 12:39:20.42
Code: 0xC004706B Source: SSIS.Pipeline
Description: failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2019-11-15 12:39:20.42
Code: 0xC004700C Source: SSIS.Pipeline
Description: One or more component failed validation. End Error Error: 2019-11-15 12:39:20.43 Code: 0xC0024107 Source: Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:39:19 PM Finished: 12:39:20 PM Elapsed: 1.234 seconds. The package execution failed. The step failed.
The simplified version of the statement which fails validation looks like this:
IF ( SELECT COUNT(*) FROM sys.[tables] WHERE [is_ms_shipped] = 0 AND modify_date > '10/24/2019 11:33:00 AM' ) > 0 BEGIN SELECT TOP 1 1 [TableName] FROM ( SELECT TOP 1 1 [TableName] FROM sys.[tables] AS T WHERE T.[is_ms_shipped] = 0 AND T.modify_date > '10/24/2019 11:33:00 AM' UNION ALL SELECT TOP 1 1 [TableName] FROM sys.views AS T WHERE T.[is_ms_shipped] = 0 AND T.modify_date > '10/24/2019 11:33:00 AM' ) q END; ELSE BEGIN SELECT TOP 1 1 [TableName] FROM sys.[tables] WHERE 1 = 0; END
Both selects return the same columns with the same data types. I know that I can rewrite this statement so that no error is returned. And I did this for a couple of packages and deployed them.
However, the problem is that the packages cannot be easily replaced due to internal reasons. I need to figure out what happened and why this error appeared all of a sudden for all the packages and to restore the conditions in which the packages worked. On the day before the issue the following Windows updates were installed on the machine where SSIS servers is running:
What I did:
I checked the versions of the ODBC Driver and aligned them with the versions that are installed on another environment where this same package still executed successfully.
There are the following platform versions that are used:
Thanks in advance for any suggestions.
User contributions licensed under CC BY-SA 3.0