SQL Server Performance

manipulate one input table and populate data into multiple tables

Discussion in 'SQL Server 2008 Integration Services' started by WingSzeto, Mar 17, 2009.

  1. WingSzeto Member

    Both of my source data and destination data are a SQL table. I want to retrieve 15 out of 50 columns in this source table and manipulate two of 15 columns(i.e. calcluation and string concatenation) to create two calculated colomns. Then I would write 12 columns of data into one table and 5 others into another table.
    I read about the For-each loop and the ADO Enumerator and thinking that I should do the data manipulation in there but I got stuck in the for-each loop as of what task I should use.
    I am a beginner in SSIS and like to get some pointers about how to do this. If there is a link on someone who has done this already, please post it.
    Any help on this is very much appreicated.
    wingman
  2. patel_mayur New Member

    I am assuming that you want to insert all the records from source table to destination tables. There is no need to use For Each Loop.
    Create one data flow task and under dataflow task
    1. Create "OLE DB Source" whereas you will specify your source table / query.
    2. Put "Derived Column" for transformation and populate two new columns using available columns from your source.
    3. Use "Multi Cast" to create couple of copies of your datasource with two newly added columns.
    4. Use two different "OLE DB Destination" for copying the required columns from multi casted recordsets.
  3. WingSzeto Member

    Thank for the pointers. It helps. I do have some follow-up questions.
    1. can you explain in what situation I should use For-each loop? .
    2. One of the derived columns I tried to create is an increment field based on an existing field in a destination table. Here is what I try to do. In the "Derived Column" transformation, I typed in the derived column name and as "Add as new colum". In the expression, I try to do a 'Select max(exployee_ID)+1 from tblemployee' to assign next value into the derived column. THe expression gave me the error, which obvisiously doesn't like the 'Select' statement. How should I do this calculation?
    wingman
  4. patel_mayur New Member

    [quote user="WingSzeto"]
    1. can you explain in what situation I should use For-each loop? .
    If you want to process your recordset record by record then you can use For Each Loop. For example if you have emailids of individuals stored in particular column and you want to send the mail to each individual. Other example could be; if you want to update the value of particular column based on the previous record's value then you can use for each loop
    2. One of the derived columns I tried to create is an increment field based on an existing field in a destination table. Here is what I try to do. In the "Derived Column" transformation, I typed in the derived column name and as "Add as new colum". In the expression, I try to do a 'Select max(exployee_ID)+1 from tblemployee' to assign next value into the derived column. THe expression gave me the error, which obvisiously doesn't like the 'Select' statement. How should I do this calculation?
    You can create "Identity Column" in your destination table which will generate sequential number. You have functions/TypeCast/Operator available on the right hand side of "Derived Column" that you can use.
    [/quote]

Share This Page