Monday, May 12, 2014

SQL SSIS 2012 - Metadata could not be determined


The metadata could not be determined because the statement 'SELECT   TOP 0 *
                           FROM   (SELECT 1,1,1,1,1,1,'none') as a (BatchID, LoadCount, FailedCount, SampleCo' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth' is not compatible with the statement 'SELECT  a.*, b.FilePath as FilePath
                                  FROM   @OUT a
                                  LEFT
                                  JOIN   dbo.BULKLOAD_Basic b (no' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth'.

Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'SELECT   TOP 0 *
                           FROM   (SELECT 1,1,1,1,1,1,'none') as a (BatchID, LoadCount, FailedCount, SampleCo' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth' is not compatible with the statement 'SELECT a.BatchID, a.LoadCount, a.FailedCount, a.SampleCount, a.BadCustomerRows, a.BadMediaRows, b.Fi' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth'.

declare @p1 int
set @p1=0
exec sp_prepare @p1 output,NULL,N'EXEC dbo.ETL_DATLOAD_SAVEBatch_Bandwidth',1
select @p1


exec [sys].sp_describe_first_result_set N'EXEC dbo.ETL_DATLOAD_SAVEBatch_Bandwidth',NULL,1

EXEC sys.sp_describe_first_result_set N'EXEC sp_who2'

SSIS 2012 is unable to generate the first result set when the stored procedure uses a temporary table. We need to provide the output result set as below to prevent the above error.

EXEC dbo.ETL_DATLOAD_SAVEBatch_Bandwidth
WITH RESULT SETS
(
       (
              BatchID int,
              LoadCount int,
              FailedCount int,
              SampleCount int,
              BadCustomerRows int,
              BadMediaRows int,
              FilePath varchar(500)
       )
)

No comments:

Post a Comment