January 28, 2010 at 11:17 am
In my OLEDB Source, I call a stored procedure which has 3 parameters. When these are hardcoded, the columns in my metadata are fine and there is no issue with my OLEDB Destination.
However when I remove the hardcoded parameters and add in 3 proper parameters (?), an error shows up on the Destination
Error3Validation error. Import Data: DTS.Pipeline: input column "RouterAccountID" (4997) has lineage ID 8666 that was not previously used in the Data Flow task.ImportDMToDebtDialler.dtsx00
I have ValidateExternalMetadata set to False but cannot seem to get around this. Has anyone got any ideas?
Thanks in advance.
January 29, 2010 at 1:08 am
January 29, 2010 at 2:45 am
wschampheleer (1/29/2010)
Do the parameters have an impact on what columns or datatypes are returned from the SP? Can you post the code of the SP?
Hi Willem,
The parameters shouldn't affect teh column, but as I look at it, I wonder is it the fact that the parameters are used in functions. Here is the code...
ALTER PROCEDURE [dbo].[usp_Extract_DM_DataForDialler]
@InsDownDECIMAL(18,0)
, @ExcludeRoutersVARCHAR(50)-- 'LGL, EXT, REC'
, @ResultCodesToExcludeVARCHAR(256)-- '053, 52, 001'
AS
SET NOCOUNT ON
BEGIN
DECLARE @ERRINT
/* Make sure that all of the settings are valid */
IF @InsDown < 0 GOTO INVALID_PARAMETERS
/* Perform the extraction */
SELECT Top 100
Ra.RouterAccountID
,PRIMARY_CUST.CustomerIDAS [UniqueKey]
,CASE RA.ClientReferenceWHEN '' THEN NULL ELSE RTRIM(RA.ClientReference)ENDAS [ClientReference]
,RTRIM(dbo.fn_AgreementNumber(RA.RouterAccountID))AS [AgreementNumber]
,PRIMARY_CUST.[PrimaryCustomer]AS [PrimaryCustomer]
,PRIMARY_CUST.[PrimaryAddressFirstLine]AS [PrimaryAddressFirstLine]
,PRIMARY_CUST.[PrimaryAddressCounty]AS [PrimaryAddressCounty]
,dbo.fn_CustomersDOB(Ra.RouterAccountID, 1)AS [PrimaryDOB]
,dbo.fn_CustomersName(RA.RouterAccountID, 2)AS [SecondaryCustomer]
,SECONDARY_CUST.[SecondaryAddressFirstLine]AS [SecondaryAddressFirstLine]
,SECONDARY_CUST.[SecondaryAddressCounty]AS [SecondaryAddressCounty]
,dbo.fn_CustomersDOB(Ra.RouterAccountID, 2)AS [SecondaryDOB]
,dbo.fn_ResolveBusinessArea(Ra.RouterAccountID)AS [ProductType]
,dbo.fn_Arrears(Ra.RouterAccountID)AS [CurrentArrears]
,Ra.CurrentBalanceAS [OutstandingBalance]
,dbo.fn_CustomersNumber(Ra.RouterAccountID, 1, 'MOB')AS [PrimaryMobile]
,dbo.fn_CustomersNumber(Ra.RouterAccountID, 1, 'HOM')AS [PrimaryHome]
,dbo.fn_CustomersNumber(Ra.RouterAccountID, 1, 'WRK')AS [PrimaryWork]
,dbo.fn_CustomersNumber(Ra.RouterAccountID, 2, 'MOB')AS [SecondaryMobile]
,dbo.fn_CustomersNumber(Ra.RouterAccountID, 2, 'HOM')AS [SecondaryHome]
,dbo.fn_CustomersNumber(Ra.RouterAccountID, 2, 'WRK')AS [SecondaryWork]
,PRIMARY_CAW.ContactAtWorkAs [PrimaryContactatWork]
,SECONDARY_CAW.ContactAtWorkAs [SecondaryContactatWork]
,EXPOSURE.ExposureAS [CustomerExposure]
,CONVERT(VARCHAR, dbo.fn_ConvertDate(EDD.DueDate), 103)AS [DueDate]
,CASE RA.StatusClass3CodeWHEN '' THEN NULL ELSE RTRIM(RA.StatusClass3Code)ENDAs [Code04]
,CASE RA.RouterTypeIDWHEN '' THEN NULL ELSE RTRIM(RA.RouterTypeID)END AS [CurrentRouter]
,EDDReject.DDRejectDateAS [DDRejectDate]
,EDR.DDRejectReasonCodeAS [DDRejectReasonCode]
,CONVERT(VARCHAR,dbo.fn_ConvertDate(RA.LastAccountPostingDate), 103)AS [FinalBillingDate]
,CONVERT(VARCHAR,dbo.fn_ConvertDate(RA.FirstAccountPostingDate ) , 103)AS [FirstBillingDate]
,PRIMARY_CUST.[Pri_Gender]AS [Pri_Gender]
,SECONDARY_CUST.[Sec_Gender]AS [Sec_Gender]
,CAST(dbo.fn_INSDown(Ra.RouterAccountID) AS VARCHAR)AS [InstallmentsDown]
,CAST(INS.InstallmentAmount AS VARCHAR)AS [InstallmentAmount]
,EDVLedger.LedgerTypeAS [LedgerType]
,CAST(dbo.fn_LTV(RA.RouterAccountID) AS VARCHAR)AS [LTV]
,PRIMARY_CUST.[Pri_MStatus]AS [Pri_MStatus]
,SECONDARY_CUST.[Sec_MStatus]AS [Sec_MStatus]
,EDD_A.NextBillingDateAS [NextBillingDate]
,PRIMARY_CUST.Pri_OccupationAS [Pri_Occupation]
,SECONDARY_CUST.[Sec_Occupation]AS [Sec_Occupation]
,CASE RA.PaymentMethodWHEN '' THEN NULL ELSERTRIM(RA.PaymentMethod) END AS [PaymentMethod]
,CAST(Ra.RouterAccountID AS VARCHAR)AS [RouterAccount]
,EDV.DelinquencyDaysAS [DelinquencyDays]
,EDT_A.IntroducerID AS [IntroducerID]
,WQ.DateActioned AS [ManualReviewDate]
,EDV_DTV.DTV AS [DTV]
,EDV_B.ExpectedLoss AS [ExpectedLoss]
,dbo.fn_Brand_BTLFlag(Ra.RouterAccountID)AS [BuyToLet]
,EDV_B.DivSecExec AS [DivSecExec]
,dbo.fn_dialler_ssis_ResultCode(RA.RouterAccountID)AS [ResultCode]
,dbo.fn_Dialler_SSIS_ResultCodeDate(RA.RouterAccountID)AS [ResultCodeDate]
, RA.LoadingDateAS [LoadingDate]
,EDD.InceptionDateAS [InceptionDate]
FROM
VW_DM_RouterAccountAS RA
LEFT OUTER JOIN-- This is the details of the Primary customer
vw_dm_DebtItemAS DI
ON
DI.RouterAccountID = RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_DateActioned_ValuesAS WQ
ON
WQ.RouterAccountID = RA.RouterAccountID
----DD Reject Date, DD Reject Reason Code
LEFT OUTER JOIN
vw_Dialler_DDRejectReasonCode_Valuesas EDR
ON
EDR.RouterAccountID = RA.RouterAccountID
--
LEFT OUTER JOIN
vw_Dialler_InceptionDate_DueDate_ValuesAS EDD
ON
EDD.RouterAccountID = RA.RouterAccountID
----Next billing date
LEFT OUTER JOIN
vw_Dialler_NextBillingDate_Values AS EDD_A
ON
EDD_A.RouterAccountID = RA.RouterAccountID
--DD Reject Date
LEFT OUTER JOIN
vw_Dialler_DDRejectDate_ValuesAS EDDReject
ON
EDDReject.RouterAccountID = .RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_LedgerType_ValuesAS EDVLedger
ON
EDVLedger.RouterAccountID = RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_DTV_ValuesAS EDV_DTV
ON
EDV_DTV.RouterAccountID = RA.RouterAccountID
--Expected Loss
LEFT OUTER JOIN
vw_Dialler_ExpectedLoss_DivSecExec_ValuesAS EDV_B
ON
EDV_B.RouterAccountID = RA.RouterAccountID
--IntroducerID
LEFT OUTER JOIN
vw_Dialler_IntroducerID_ValuesAS EDT_A
ON
EDT_A.RouterAccountID = RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_InstallmentAmount_ValuesAS INS
ON
INS.RouterAccountID = RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_DelinquencyDays_ValuesAS EDV
ON
EDV.RouterAccountID = RA.RouterAccountID
INNER JOIN
vw_Dialler_PrimaryCustomer_ValuesAS PRIMARY_CUST
ON
PRIMARY_CUST.RouterAccountID = RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_ContactAtWork_ValuesAS PRIMARY_CAW
ON
PRIMARY_CAW.CustomerID = PRIMARY_CUST.CustomerID
LEFT OUTER JOIN
vw_Dialler_SecondaryCustomer_ValuesAS SECONDARY_CUST
ON
SECONDARY_CUST.RouterAccountID = RA.RouterAccountID
LEFT OUTER JOIN
vw_Dialler_ContactAtWork_ValuesAS SECONDARY_CAW
ON
SECONDARY_CAW.CustomerID = SECONDARY_CUST.CustomerID
LEFT OUTER JOIN
vw_Dialler_CustomerExposure_ValuesAS EXPOSURE
ON
EXPOSURE.CustomerID = PRIMARY_CUST.CustomerID
WHERE
dbo.fn_INSDown(Ra.RouterAccountID) >= @InsDown
AND
RA.RouterTypeID NOT IN(
SELECT
Element
FROM
dbo.fn_Dialler_SSIS_SplitList(@ExcludeRouters, ',')--TODO can this delimiter be obtained form the settings table???
)
AND
dbo.fn_Dialler_SSIS_ResultCode(RA.RouterAccountID)
NOT IN(
SELECT-- Bad Number Codes
Element
FROM
dbo.fn_Dialler_SSIS_SplitList(@ResultCodesToExclude, ',')
)
RETURN 0
INVALID_PARAMETERS:
RETURN -1
END
January 29, 2010 at 2:58 am
My first guess would be that the problem lies in the fact that your stored procedure can return two different result sets - depending on the value of @InsDown.
Since you can't use the return value in the data flow anyway, I suggest to rework the SP as follows;
Remove the initial check (IF @InsDown < 0 GOTO INVALID_PARAMETERS) and the part starting at RETURN 0.
Add the following to your WHERE clause
AND @InsDown >= 0
January 29, 2010 at 7:33 am
I have tried this but to no avail unfortunately and it is acting as before.
I can add one parameter, the InsDown without issue, but when I overwrite the hardcoded 2nd parameter with a ?, I can no longer view the columns and it is at this stage that the package breaks.
January 29, 2010 at 9:49 am
Hey,
Just for anyone that may come across this, I resolved the error by simply adding SET FMTONLY OFF to teh stored proc and now the metadata ia aalways returned.
January 29, 2010 at 10:49 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply