none
Control Flow looping design with different control variables

    Question

  • I am now working on staging input of the DWH part. I'd like to design Data task flows that run parallel and control by parameter from variables.
    Let's say that  I will have Data Task Flow1 that extracts OLEDB source and keeps different value for more than 1 record into records set. 
    Then, information in recordset need to be stored in variables e.g. ProdID = '001','002', PropCode = 'K11','K12'

    Then the variables e.g. the one which keeps ProdID ='001' needs to run data task flow 2.1 
    and the one with ProdID ='002' needs to run data task flwo 2.2

    Inside both data task flow 2.1 and 2.2, I would like to use its own PropCode as well to insert data in derived new column for their own destination table.

    Please suggest how can I design all this thing. With Loop container or For each loop container? and how?
    Thursday, July 11, 2013 1:27 PM

Answers

All replies

  • To run in parallel with dynamic variables shared across DFTs may be not what you want, but I don't have enough details on the exact design to conclude on anything. So, to run in parallel you simply don't connect the DFTs together. You would need to use the Precedence Constraints - https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/ driven by values from a shredded ADO recordset: http://www.sqlis.com/sqlis/post/Shredding-a-Recordset.aspx. A precaution: dumping a lot of data into an ADO Recordset destination may be a bad idea.

    Arthur My Blog

    Thursday, July 11, 2013 2:03 PM
  • Hi,

    To be exact what I would like to do is..

    I have these two records stored in the recordsets

    ID PropCode ProdID

    1 K11 001

    2 K12 002

    PropCode is the name of table I would like to extract, i.e. 

    select * from dbo.K11$Product1

    select * from dbo.K12$Product2

    I would like to extract data from both table into dbo.StageProducts by adding 1 additional field to dbo.StageProducts.That additional field is ProdID. 

    Now both dbo.K11$Product1 and dbo.K12$Product2 contain many many of records, 
    which I would like them to run paralelly, otherwise it consumes too much time.

    How can I use variables with control flow in this case?

    PropCode is now kept in variable (I use SQL from variable in one of data task).

    And I already use shredding recordset and variables in the design you mentioned, but it doesn't seem enough.

    Thanks.

    Thursday, July 11, 2013 2:33 PM
  • Adding field (column) dynamically? if so this can only be done by creating the package programmatically.

    Arthur My Blog

    Thursday, July 11, 2013 3:42 PM