locked
Pass query between SQL and Teradata RRS feed

  • Question

  • I have the following query in SQL.
    Select distinct UnitNumbers from table1

    In Teradata I have the following query:
    Select  Empname from Table where UnitNumber = (I need to pass the unitnumber I got from the Sql Query).
    The result obtained from this Teradata query has to be passed back to Sql table2.

    How to do this in SSIS?.  Any suggestion would be helpful.  Thanks.
    NSG12
    Thursday, February 26, 2009 5:06 PM

Answers

  • You can consider doing the following:

    Create 2 variables

    • Dataset (Type: Object)
    • UnitNumbers (Type: Int32)



    Control Flow

    • Execute SQL Task
      • General
        • ResultSet : Full result set
        • SQL Statement: Select distinct UnitNumbers from table1
      • Result Set
        • ResultName: 0 Variable Name: User::Dataset
    • Foreach Loop Container
      • Collection:
        • Choose the Foreach ADO Enumerator
        • ADO Object source variable: User::Dataset
        • Enumeration mode: Rows in the first table
      • Variable Mappings:
        • User:: UnitNumbers    Index: 0
      • Consists of a Data Flow Task

    Data Flow Task

        • Source
          • Make use of a Source that connects to Teradata (e.g. Attunity Teradata Source)
          • If you are using the Attunity Teradata Source, you can use the Expressions to set the [Teradata Souce].[SqlCommand] to 
            Select  Empname from Table where UnitNumber = <variable name> (In the expression editor, you can reference the UnitNumbers variable here)
        • SQL Server Destination or OLE DB destination (which writes to Sql table 2)

    Thank you.

    • Proposed as answer by weehyongEditor Friday, February 27, 2009 7:21 AM
    • Marked as answer by Tony Tang_YJ Thursday, March 5, 2009 10:45 AM
    Friday, February 27, 2009 7:18 AM
    Answerer
  •  here's how i would approach the problem.

    (1) create an ssis variable to hold the unit numbers.  set the data type to System.Object.

    (2) create an ole db connection manager that points to the sql server database.

    (3) configure an execute sql task to retrieve the unit numbers result set into the ssis variable created in step (1).  also configure this task to use the connection manager created in step (2)

    (3) attach a foreach loop container to the execute sql task via a precedent constraint.

    (4) configure the foreach loop container to use an ado enumerator and use the variable populated by the execute sql task as the source variable.

    (5) create another ssis variable.  this variable will hold each unit number when the foreach loop container interates over the resultset variable.  set the data type to int32.

    (6) configure the foreach loop container created in step (3) to map to the variable created in step (5)

    (7) create an ole db connection manager that points to the teradata database.

    (8) place a data flow task inside of the foreach loop container created in step (3).  set DelayValidation = True.

    (9) place an ole db source component inside of the data flow task created in step (8).  configure this component to use the connection manager created in step (7).  set the data access mode to: sql command.  set the sql command text to: Select  Empname from Table where UnitNumber = ?.  set parameter 0 to: the variable created in step (5).

    (10) if the sql server is 2005 or 2008, add a sql server destination component to the data flow.  otherwise, add a ole db destination component to the data flow.  connect the the ole db source created in step (9) to this destination.

    (11) configure the destination created in step (10) to use the connnection manager created in step (2).  set it to point to: table2.  map the columns as appropriate.

    hth.


    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by weehyongEditor Friday, February 27, 2009 7:21 AM
    • Marked as answer by Tony Tang_YJ Thursday, March 5, 2009 10:45 AM
    Friday, February 27, 2009 7:19 AM

All replies

  • You can consider doing the following:

    Create 2 variables

    • Dataset (Type: Object)
    • UnitNumbers (Type: Int32)



    Control Flow

    • Execute SQL Task
      • General
        • ResultSet : Full result set
        • SQL Statement: Select distinct UnitNumbers from table1
      • Result Set
        • ResultName: 0 Variable Name: User::Dataset
    • Foreach Loop Container
      • Collection:
        • Choose the Foreach ADO Enumerator
        • ADO Object source variable: User::Dataset
        • Enumeration mode: Rows in the first table
      • Variable Mappings:
        • User:: UnitNumbers    Index: 0
      • Consists of a Data Flow Task

    Data Flow Task

        • Source
          • Make use of a Source that connects to Teradata (e.g. Attunity Teradata Source)
          • If you are using the Attunity Teradata Source, you can use the Expressions to set the [Teradata Souce].[SqlCommand] to 
            Select  Empname from Table where UnitNumber = <variable name> (In the expression editor, you can reference the UnitNumbers variable here)
        • SQL Server Destination or OLE DB destination (which writes to Sql table 2)

    Thank you.

    • Proposed as answer by weehyongEditor Friday, February 27, 2009 7:21 AM
    • Marked as answer by Tony Tang_YJ Thursday, March 5, 2009 10:45 AM
    Friday, February 27, 2009 7:18 AM
    Answerer
  •  here's how i would approach the problem.

    (1) create an ssis variable to hold the unit numbers.  set the data type to System.Object.

    (2) create an ole db connection manager that points to the sql server database.

    (3) configure an execute sql task to retrieve the unit numbers result set into the ssis variable created in step (1).  also configure this task to use the connection manager created in step (2)

    (3) attach a foreach loop container to the execute sql task via a precedent constraint.

    (4) configure the foreach loop container to use an ado enumerator and use the variable populated by the execute sql task as the source variable.

    (5) create another ssis variable.  this variable will hold each unit number when the foreach loop container interates over the resultset variable.  set the data type to int32.

    (6) configure the foreach loop container created in step (3) to map to the variable created in step (5)

    (7) create an ole db connection manager that points to the teradata database.

    (8) place a data flow task inside of the foreach loop container created in step (3).  set DelayValidation = True.

    (9) place an ole db source component inside of the data flow task created in step (8).  configure this component to use the connection manager created in step (7).  set the data access mode to: sql command.  set the sql command text to: Select  Empname from Table where UnitNumber = ?.  set parameter 0 to: the variable created in step (5).

    (10) if the sql server is 2005 or 2008, add a sql server destination component to the data flow.  otherwise, add a ole db destination component to the data flow.  connect the the ole db source created in step (9) to this destination.

    (11) configure the destination created in step (10) to use the connnection manager created in step (2).  set it to point to: table2.  map the columns as appropriate.

    hth.


    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by weehyongEditor Friday, February 27, 2009 7:21 AM
    • Marked as answer by Tony Tang_YJ Thursday, March 5, 2009 10:45 AM
    Friday, February 27, 2009 7:19 AM