none
How to pass parameter values to the stored procedure dynamically in ssis

    Question

  • Hi All,

      I have created a ssis package ,Which is internally calling a stored procedure with single input parameter(Interval).My stored procedure logic will execute data for every 6 months of every execution of package and dumped into .csv file. For Example,If the Interval is 1 then it takes the startdate as 2010-07-01 and enddate as 2010-12-31 ,similarly If the Interval is 2 then it takes the startdate as 2011-01-01 and enddate as 2011-06-30 ......etc . My question is How can i pass Interval as 1,2,3,4(for two Years data) to the stored procedure through ssis? I am able to pass 1 value to the interval variable and it has created .csv file with 6 months data but next time i am unable to pass 2 value to the interval,similarly 3,4.

    Please anybody suggest me on this ?

    Thanks ,

    Visu
    Sunday, December 09, 2012 2:47 PM

Answers

  • Hi,

    So required output is different file for each iteration, right?

    Your solution is based on Stored procedure. If the stored procedure create file, you have to change stored procedure (which is in dirrerent forum).

    If you are using stored procedure only for querying data and for export to file you use flatFileDestination, you can set FlatFile to only add new rows to file or you can configure FlatFile Connection manager to change file name (connection string property)

    PS: I'm not a native speaker, so maybe i'm asking for allreadey answered questions.

    Best solution for me is use procedure only for querying data as data source, then connect it to FlatFile Data destination and then parametrize it by changing connection string for every iteration.

    Zdenek


    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

    • Proposed as answer by Eileen Zhao Friday, December 14, 2012 9:30 AM
    • Marked as answer by Eileen Zhao Tuesday, December 18, 2012 6:33 AM
    Monday, December 10, 2012 10:09 AM

All replies

  • Hi,

    I'm not sure what is your question. Respectively what is not working.

    If you are not able to call procedure with parameter, then make 2 variables. First int called Interval and second one called tsqlCallProc of string data type.

    Set variable Interval to required value. For tsqlCallProc variable set property evaluateAsExpression to true and create Expression (another propertry) by clicking ...

    Expression will be something like

    ="exec YourProcedureName " + (DT_wstr,5)@Interval
    Where @Interval means variable Interval (@ is not part of Variable Name)

    For running automatically you can consider:

    A. If you wants to call store procedure multiple times in one run, take a look for For Loop container.

    B. If you wants to call package with parameter (i.e. agent job which call package and send value) then try to look for

    Or if this is not answer then please be more specific

    Zdenek


    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

    Sunday, December 09, 2012 7:35 PM
  • Thanks for the reply Zdenek,

       i am trying to pass input parameter to the stored procedure through SSIS Package. If i pass the interval as 1 to the stored procedure through the ssis package it should dump the 6 months data into .csv file. Similarly it should dump the data into same.csv file ,when i pass the interval as 2,3,4. I tried with the Forloop Container but its dumping data once complete all iterations but my requirement is it should dump data into .csv file for every iteration. Is this possible? Please share the script or steps to resolve this issue.

    Monday, December 10, 2012 5:56 AM
  • You can use execute SQL task and pass the parameters to a stored procedure

    http://decipherinfosys.wordpress.com/2008/04/14/execute-sql-tasks-using-stored-procedures/

    http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

    Regards,Eshwar.


    --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Monday, December 10, 2012 6:14 AM
  • Hi,

    So required output is different file for each iteration, right?

    Your solution is based on Stored procedure. If the stored procedure create file, you have to change stored procedure (which is in dirrerent forum).

    If you are using stored procedure only for querying data and for export to file you use flatFileDestination, you can set FlatFile to only add new rows to file or you can configure FlatFile Connection manager to change file name (connection string property)

    PS: I'm not a native speaker, so maybe i'm asking for allreadey answered questions.

    Best solution for me is use procedure only for querying data as data source, then connect it to FlatFile Data destination and then parametrize it by changing connection string for every iteration.

    Zdenek


    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

    • Proposed as answer by Eileen Zhao Friday, December 14, 2012 9:30 AM
    • Marked as answer by Eileen Zhao Tuesday, December 18, 2012 6:33 AM
    Monday, December 10, 2012 10:09 AM