none
Run loop in Data Flow task

    Question

  • Hi all ,

    I need update data base table in loop  inside Data Flow task,

    how can i do it ,please ?

    Thanx

    Monday, September 30, 2013 7:20 AM

Answers

  • Evgeniy.co.il,

    This is what I suggest:

    In your data flow:

    In your control flow:

    Cheers,

    Guillaume

    • Marked as answer by Evgeniy.co.il Monday, September 30, 2013 9:39 AM
    Monday, September 30, 2013 9:25 AM

All replies

  • Hi,

    You cannot make a loop inside the data flow task. Why would you want to do that?

    What you can do, is run data flow task in a loop. In my opinion this is useful only if you are changing properties of the data flow task or connection managers being used in data flow task.


    HTH, Regards, Dean Savović

    Monday, September 30, 2013 7:28 AM
  • Hi,

    If you are planning a loop mechanism, you can use tasks such as For Each Loop (which will loop for all tables in a database) with an Execute SQL Task which will have the UPDATE logic.  


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Monday, September 30, 2013 7:52 AM
  • Hi Dean , thank you for your answer,

    inside Data Flow task i have data stream , that grouped by some key, for each value of the key in stream i want to run some manipulation in script component and after that update value for this key in data base table by OLE DB commander, how can i do it ? If i wan't do it from script component (Open command object , set connection and something else ...) ?

    Regards, Evgeniy.


    Monday, September 30, 2013 8:01 AM
  • Hi Evgeniy.co.il,

    What you could do is:

    1. Keep what you are doing in the Data Flow task but store the result in a table (only insert)
    2. In a SQL task, do your update by joining the table from the previous step with the final table

    Cheers,

    Guillaume

    Monday, September 30, 2013 8:07 AM
  • Hi Guillaume , hi Raunak ,

    Thanx for yours answers,

    My task is call to WCF for each key of the data stream from script component , and after that , synchronously,for same key update data base table in same transaction. If i understand right i can do it only from script component , using by ADO net objects ?

    Regards , Evgeniy.


    Monday, September 30, 2013 9:04 AM
  • Hi Evgeniy,

    If I understood you correctly you need to perform some transformation using script component based on key value from your source data stream.

    Are these key values from your source data stream static or dynamic. If key values are some static list you could use Conditional Split transformation in Data Flow task and for each condition (key value based condition) do the Script Component transformation. After that use Union transformation to group the stream back together.

    If key values are dynamic (they change based on some conditions or are read from a table), then you will have to use For Each Loop container and Data Flow task inside it. Data source in Data Flow task will have dynamic Data Access Mode (sql command with parameters or sql command from variable where variable is For Each Loop or higher scoped and is build as expression taking key value into account).


    HTH, Regards, Dean Savović

    Monday, September 30, 2013 9:17 AM
  • Evgeniy.co.il,

    This is what I suggest:

    In your data flow:

    In your control flow:

    Cheers,

    Guillaume

    • Marked as answer by Evgeniy.co.il Monday, September 30, 2013 9:39 AM
    Monday, September 30, 2013 9:25 AM
  • Thank you Dean ,

    it's very helpfull

    Monday, September 30, 2013 9:39 AM
  • Hi Guillaume,

    thank you for your answer , the picture gives me some new ideas

    Monday, September 30, 2013 9:40 AM