How to Pass parameters in SSIS dataflow

Hot Topic

error when trying to connect to jdbc
A lock could not be acquired after 16 attempts and timed out
Schema and principals (Sql Server 2005)
Passing Collections to Custom Code
Log Shipping Error
What does "layout failed validation" mean?
Synch
SQL Express & WinXP
sql stored procedure question

SQL Server

Range Attribute
Sorting records in a Group By
Report Viewer webpart -- Custom development
Cannot concatenate parameter to the sql string
Maximum length sql statement that can be run in SQL CE
Connecting Sql Server 2005 from ASP?
sys.conversation_endpoints
Issues while loading in parallel into a partitioned table
Problem creating SQL Server Mobile Subscription
Generating a PDF type report and embeding fonts

How to Pass parameters in SSIS dataflow

I am using a parameterized select query as displayed below to fetch values from source.

SELECT A.Account_GUID,
M.Merchant_GUID,
H.Household_GUID,
B.BankAU_GUID,
SR.SalesRep_GUID,
E.Entitlement_GUID,
I.Income_GUID,
Exp.Expense_GUID,
SP.Sales_Product_GUID,
P.Product_cd,
SUM(S.Sales) AS Monthly_gross_MC_VI_amt,
SUM(S.Sales) - SUM(S.[Returns]) AS Monthly_net_MC_VI_amt,
SUM(S.SaleTxns) AS Monthly_gross_MC_VI_tran_cnt,
SUM(S.SaleTxns) - SUM(S.ReturnTxns) AS Monthly_net_MC_VI_tran_cnt
FROM Account AS A
LEFT OUTER JOIN dbKAIExtract.dbo.tblSales_STG AS S
ON A.Account_No = S.AccountNo
And S.BucketNo = And S.ProductCode in ('01','02')
LEFT OUTER JOIN Merchant AS M
ON A.Account_No = M.Account_no
INNER JOIN SalesRep AS SR
ON SR.Rep_SSN = isnull(A.rep_SSN,'000000000')
INNER JOIN Household AS H
ON A.Account_No = H.Account_no
LEFT OUTER JOIN BankAU AS B
ON A.Assigned_AU = B.AU_No
LEFT OUTER JOIN SalesProduct AS SP
ON A.Account_No = SP.Account_no
And SP.Reporting_Interval_Id =
LEFT OUTER JOIN Entitlement AS E
ON E.Account_no = A.Account_No
AND SP.Product_Cd = E.Entitlement_Card_Type
LEFT OUTER JOIN Income AS I
ON I.Account_no = A.Account_No
And I.Reporting_Interval_Id =
LEFT OUTER JOIN Expense AS Exp
ON Exp.Account_no = A.Account_No
And EXP.Reporting_Interval_Id =
LEFT OUTER JOIN Product AS P
ON P.Product_cd = SP.Product_cd
WHERE (A.current_ind = 1)
AND (SR.current_ind = 1)
GROUP BY A.Account_GUID,
M.Merchant_GUID,
H.Household_GUID,
B.BankAU_GUID,
SR.SalesRep_GUID,
E.Entitlement_GUID,
I.Income_GUID,
Exp.Expense_GUID,
SP.Sales_Product_GUID,
P.Product_cd

My problem is, I am not able to assign any variables to parameterized query. Can any body guide how to assign respective variables to the parameterized query. I have the above query as a part of OLE DB Source step within Data flow task.

Thank you

Jatin




Answer this question

How to Pass parameters in SSIS dataflow

  • mark chen

    Jatin,

    I'm not sure. Perhaps they can only be used in WHERE clauses. I personally never use them so am not sure.

    Why are you averse to using an expresson Its easier I reckon.

    -Jamie



  • ke.zhou

    Sounds this is the good thread to discuss the parameter mappings to user variables

    http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=899884&SiteID=1



  • Marcelo Diniz

    that solution only applies to OLE DB data source but how about a datareader source. Is there anyway to access variables to generate a dynamic sql statement against an ODBC db through the datareader source


  • Jafo7822

    Hello Jamie,

    I already implemented my logic according to expression. That is working fine. Just wanted to know if, I was doing anything wrong by passing parameters the way I have explained.

    Thank You

    Jatin Shah



  • Mechamonkey

    Jamie,

    I tried passing " " as a part of my parameterized query. It allowed me to select parameters to be passed when " " was part of Where clause but if I have to pass a parameter to an inline query or amoung any other join condition within the query it raises following error :

    Parameter cannot be extracted from the SQL Command. The Provider might not help to parse parameter information from the command. In that case, use the "SQL Command from variable" access mode, in which the entire SQL command is stored in a variable.

    Thank you

    Jatin Shah.



  • EESTIC

    Jaime,

    I am also trying to pass a parameter in the DataReader Source and did what you told Quame to do. But the output columns do not show up in the DataReader Source. This is what I have in the Expression Builder:

    "exec udsp_Stored_Proc_Name '" + @[User::MDate] + "',' " + (DT_WSTR, 1) @[User::Initial] + "'". Both user variables are declared as string datatype. When I click the Evaluate Expression button, this is what I get:

    exec udsp_Stored_Proc_Name '2006-01-01',' 1'. When I execute this Data Task the error says:

    component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

    Am I missing something

    Thanks,

    cenko2


  • Barron Gillon

    Jamie,

    The SQL Statement is as below :

    SELECT A.Account_GUID,
    M.Merchant_GUID,
    H.Household_GUID,
    B.BankAU_GUID,
    SR.SalesRep_GUID,
    E.Entitlement_GUID,
    I.Income_GUID,
    Exp.Expense_GUID,
    SP.Sales_Product_GUID,
    P.Product_cd,
    SUM(S.Sales) AS Monthly_gross_MC_VI_amt,
    SUM(S.Sales) - SUM(S.[Returns]) AS Monthly_net_MC_VI_amt,
    SUM(S.SaleTxns) AS Monthly_gross_MC_VI_tran_cnt,
    SUM(S.SaleTxns) - SUM(S.ReturnTxns) AS Monthly_net_MC_VI_tran_cnt
    FROM Account AS A
    LEFT OUTER JOIN dbKAIExtract.dbo.tblSales_STG AS S
    ON A.Account_No = S.AccountNo
    And S.ProductCode in ('01','02')
    LEFT OUTER JOIN Merchant AS M
    ON A.Account_No = M.Account_no
    INNER JOIN SalesRep AS SR
    ON SR.Rep_SSN = isnull(A.rep_SSN,'000000000')
    INNER JOIN Household AS H
    ON A.Account_No = H.Account_no
    LEFT OUTER JOIN BankAU AS B
    ON A.Assigned_AU = B.AU_No
    LEFT OUTER JOIN SalesProduct AS SP
    ON A.Account_No = SP.Account_no
    And SP.Reporting_Interval_Id = (Value = 200603/200604/200605....)
    LEFT OUTER JOIN Entitlement AS E
    ON E.Account_no = A.Account_No
    AND SP.Product_Cd = E.Entitlement_Card_Type
    LEFT OUTER JOIN Income AS I
    ON I.Account_no = A.Account_No
    And I.Reporting_Interval_Id = (Value = 200603/200604/200605....)
    LEFT OUTER JOIN Expense AS Exp
    ON Exp.Account_no = A.Account_No
    And EXP.Reporting_Interval_Id = (Value = 200603/200604/200605....)
    LEFT OUTER JOIN Product AS P
    ON P.Product_cd = SP.Product_cd
    WHERE (A.current_ind = 1)
    AND (SR.current_ind = 1)
    GROUP BY A.Account_GUID,
    M.Merchant_GUID,
    H.Household_GUID,
    B.BankAU_GUID,
    SR.SalesRep_GUID,
    E.Entitlement_GUID,
    I.Income_GUID,
    Exp.Expense_GUID,
    SP.Sales_Product_GUID,
    P.Product_cd

    I have mentioned the values that needs to be passed to each parameter. The values will change depending upon the year and month in which it is executed. The values will remain the same for all columns.

    Thank you

    Jatin Shah



  • Hardik Zaveri

  • Netliner

    Jamie,

    Thank you for answering my question. But I am not able to understand, if I have to use a parametrized query is there any other way available besides the one that you have explained.

    Jatin



  • cajinamaster

    Quame,

    Go into the control-flow, select your data-flow containing the datareader source, and Press F4.

    In the properties pane expand Expressions and create a new expression on the [DataReader Source].[SqlCommand] property.

    Jatin,

    Yes, you can use a " " as a placeholder for a parameter. You then click on "Parameters" to specify what that parameter value should be. This is very similar to the DTS2000 method.

    -Jamie



  • Netmon

    Jatin,

    Can you post your SQL statement and the values you want to supply for each parameter

    -Jamie



  • How to Pass parameters in SSIS dataflow
Answer this question