passing parameters in ado.net source

Question

I wanted to pass a parameterized query in ado.net source and i rea don this post that only way to do this is to use expressions.

So here is my expression

"SELECT
    LEDGER_YR_MO,
    LOCATION,
    FDR_FND_NO,
    FDR_INVST_POOL_CD,
    FISCAL_YR_BEG,
    FISCAL_YR_END,
    POST_DATE,
    FDR_FND_TTL,
    FDR_FND_TYPE_CD,
    FDR_FND_PURP_CD,
    FDR_CR_ELIG_FL,
    FDR_ANN_CR_RATE,
    FDR_MTH_CR_RATE,
    FDR_CR_AMT,
    FDR_TR_ELIG_FL,
    FDR_ANN_TR_RATE,
    FDR_MTH_TR_RATE,
    FDR_60MTH_AVG_SHARE_PRC,
    FDR_TR_AMT,
    FDR_PROJ_GEP_INCM_AMT,
    FDR_TR_AUGMENTATION_AMT,
    FDR_GEP_NET_PAYOUT_AMT
FROM
    FS0TST.UCOP_FDR_TR_CR where 
LEDGER_YR_MO =  '"+ (DT_WSTR,30)  @[User::END_LEDGER_YR] +"'  "

but when I click evaluate the expression , I see blank instead of variable ..does anyone know the possible issue: Here is the evaluate expression looks like

SELECT
    LEDGER_YR_MO,
    LOCATION,
    FDR_FND_NO,
    FDR_INVST_POOL_CD,
    FISCAL_YR_BEG,
    FISCAL_YR_END,
    POST_DATE,
    FDR_FND_TTL,
    FDR_FND_TYPE_CD,
    FDR_FND_PURP_CD,
    FDR_CR_ELIG_FL,
    FDR_ANN_CR_RATE,
    FDR_MTH_CR_RATE,
    FDR_CR_AMT,
    FDR_TR_ELIG_FL,
    FDR_ANN_TR_RATE,
    FDR_MTH_TR_RATE,
    FDR_60MTH_AVG_SHARE_PRC,
    FDR_TR_AMT,
    FDR_PROJ_GEP_INCM_AMT,
    FDR_TR_AUGMENTATION_AMT,
    FDR_GEP_NET_PAYOUT_AMT
FROM
    FS0TST.UCOP_FDR_TR_CR where 
LEDGER_YR_MO =  ''

Check your END_LEDGER_YR parameter in package. It must be empty. Give some default value to it — dejjub-AIS, Sep 27 '16 at 10:26
And expressions is not only way for parameter queries. You can use SQL command as data access mode with the parameter as "?" and set the parameter value by clicking on parameters button and map the variable to it — dejjub-AIS, Sep 27 '16 at 11:30
@SolowDeveloper you can't do parameter mapping with ado.net source queries. That's only with OLEDB queries. — Tab Alleman, Sep 27 '16 at 12:10

score 1 · Answer 1 · answered Sep 27 '16 at 12:07

When you click the Evaluate Expression button, no code actually gets executed, so the only thing SSIS can look at is the static default value you gave your variable.

If you want to see a value instead of a blank in the Evaluate Expression, you have to give your variable a default value, even if you don't intend to use that value. When the SSIS package is executed, whatever code you use to populate the variable will overwrite the default value, so having a default value is harmless.

passing parameters in ado.net source

1 Answers1