none
ssis oledb passing variables

    Question

  • hi

    in ssis oledb source i am using sql query with joins

    i want to pick only top 10 rows some times i want to pick  top 20 rows

    i want to pass value (either 10 or 20) in variable

    select top 10 from abc a inner join dbc1 d on

    a.no=b.no

    in this top 10 values i want to pass in variable

    can u help this qustion

    thanks

    Ram

    Friday, June 21, 2013 3:54 AM

All replies

  • hi

     

    in ssis oledb source i am using sql query with joins

    i want to pick only top 10 rows some times i want to pick  top 20 rows

    i want to pass value (either 10 or 20) in variable

     

    select top 10 from abc a inner join dbc1 d on

    a.no=b.no

     

    in this top 10 values i want to pass in variable

    can u help this qustion

     

    thanks

    Ram

     

     

     

     

     

     

    Friday, June 21, 2013 3:54 AM
  • Hi Ram,

    Use a second variable which should evaluate using an expression

    your expression could something like

    ="SELECT TOP("+CStr(MyFirstVariable)+") * FROM MYTABLE"

    and then use this variable in your OLE DB Source.

    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    Friday, June 21, 2013 3:58 AM
  • hi ..some times i want to select all rows

    in that case

    what i have to pass to tht variabe

    Friday, June 21, 2013 4:00 AM
  • You have to use the expression syntax on this case.

    Select Top(@rowsToPick) * From abc.

    You should be able to map the @rowsToPick with your SSIS variables.

    Friday, June 21, 2013 4:00 AM
  • Keep the value as "100 PERCENT"  on your variable when you need to fetch all rows.

    But, you need to scarify the () on the TOP expression.

    The new query will be

    ="SELECT TOP "+CStr(MyFirstVariable)+"  * FROM MYTABLE"

    Friday, June 21, 2013 4:14 AM
  • ok actually i am using long sql query if in am putting in expression sit is showing error message more more than 4000 characters

    Friday, June 21, 2013 4:26 AM