December 16, 2015 at 4:29 pm
My SSIS package does not recognise any "Available External Columns"
in OLE DB Source properties.
Inside OLE DB Source there is SQL Command - a call
to stored procedure "FATCA_TDS_MASKED.SP_GENERATE_XML_CONTENMASKED"
When I run this procedure in SSMS I see "XML_DATA" column returned. no problem.
Why OLE DB Source does not see the column?
December 16, 2015 at 8:44 pm
I found the problem.
At the top of
FATCA_TDS_MASKED.SP_GENERATE_XML_CONTENT_MASKED
there was a call to log procedure - EXECUTE [FATCA_STG].[SP_ETL_LOG]...
It was inserting a record in LOG table.
As soon as I remove it,
SSIS package recognizes XML_DATA column , works fine.
DECLARE
@V_LOG_BUS_PROC_DT DATETIME
,@V_LOG_PROC_NAME VARCHAR(128)
......
SELECT
@V_LOG_PARENT_PROC_NAME = 'SP_GENERATE_XML_CONTENT_ANNUAL_MASKED',
@V_LOG_STATUS = 'SUCCESS'
;
EXECUTE [FATCA_STG].[SP_ETL_LOG]
@V_LOG_BUS_PROC_DT
,@V_LOG_PROC_NAME
.......
I already confused SQL Engine when jumped from SP1 to SP2.
This is probably enough.
CREATE PROC sp1
EXEC sp2
works..
CREATE PROC sp1
EXEC sp2 which calls sp3 ... it too much confusion for SSIS to estimate METADATA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply