Error details

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "
The metadata could not be determined because the statement 'SELECT '1' AS CountryCode, 'BranchCode' as BranchCode' in procedure 'SP_Reporting' is not compatible with the statement 'SELECT '-1' AS CountryCode' in procedure 'SP_Reporting'. "

This Error message was generated by SSIS when we try to call a SP and output the result of this SP to a flatfile. however, we can call the SP using SQL and get the expected result.

IF @Country = 'US'
	SELECT '1' AS CountryCode, 'BranchCode' as BranchCode
ELSE
	SELECT '-1' AS CountryCode

The SP looks fine, but SSIS cannot use it because the output is unpredicted if the input is ‘US’, the output will contains two columns, else it will contain only one column. the question for SSIS is how to map the output to the destination? how to prevent runtime error?

Probably, SSIS DataFlow cannot accept this kind of unpredicted SP.

There’s a samilar post on StackOverflow, one of the suggestions is to use SET FMTONLY ON, however, this option has been deprecated since SQL Server 2012. one of the replacements is sp_describe_first_result_set (Transact-SQL). several posts show that SSIS 2012 is using sp_describe_first_result_set. source1 souce2 however, I canot find any evidence for this conclusion. I’m going to verify it by executing EXECUTE sp_describe_first_result_set "dbo.SP_Reporting 'US'", same error message returned:

Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'SELECT '1' AS CountryCode, 'BranchCode' as BranchCode' in procedure 'SP_Reporting' is not compatible with the statement 'SELECT '-1' AS CountryCode' in procedure 'SP_Reporting'.

So the question moved to sp_describe_first_result_set:

• If the number of columns differs, an error is thrown and no result is returned.
• If the column name differs, the column name returned is set to NULL.
• It the nullability differs, the nullability returned will allow NULLs.

Solutions

• use same output format in SP, e.g.:

IF @Country = 'US'
	SELECT '1' AS CountryCode, 'BranchCode' as BranchCode
ELSE
	SELECT '-1' AS CountryCode, '' as BranchCode

• if the output format is different, split the SP to multiple SPs

code

IF (OBJECT_ID('dbo.SP_Reporting') IS NOT NULL)
  DROP PROCEDURE dbo.SP_Reporting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.SP_Reporting 
	@Country nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON;
	IF @Country = 'US'
		SELECT '1' AS CountryCode, 'BranchCode' as BranchCode
	ELSE
		SELECT '-1' AS CountryCode--, '' as BranchCode
END
GO

EXECUTE dbo.SP_Reporting 'US' 
EXECUTE dbo.SP_Reporting '' 

EXECUTE sp_describe_first_result_set "dbo.SP_Reporting 'US'"