locked
Passing csv file data as a parameter to sql query dynamically using SSIS RRS feed

  • Question

  • Hi All,

    I have a scenario in ssis where i have to pass column data from a csv file dynamically as a parameter to a sql query.I want to implement this using ssis,Please send me a solution ASAP.

    for example:
    ------------------------------------------------------------------
    Data in a text file with 3 records

    col1   col2       col3
      1     john      sam     
      2     tom      garry
      3     tim       bruce      
    -------------------------------------

    -------------------------------------
    Sql Query :

    select * from table1 where col1='1'  and col2='john'  and col3='sam'
    select * from table1 where col1='3'  and col2='tim'  and col3='bruce'

    Thanks in Advance,
    RH
    sql
    Monday, February 22, 2010 2:57 AM

Answers

  • I think you need to put the data in text file into a table and then use an Execute SQL task to fetch the result into an object variable (objtest). Create 3 variables Col1,Col2 and Col3.Then take a for each loop contaioner with ForEachADOEnumerator as Collection and select the variable objtest from the drop down box. Then go to variable mappings inside the ForEachLoop Editor and do the mappings as:    Variable      Index
        ----------------------------
            User::Col1     0
            User::Col2     1
            User::Col3     2

    Create a Variable SQL with data type as string and set its "Evaluate as Expression" property as true. Then go to its expression property and set the expression as :
    "Select * From TableName Where Col1 = " +   @[User::Col1] + " And Col2='" +  @[User::Col2] + "'" + " And Col3='" +  @[User::Col3] + "'"    
    Take a DFT inside the Foreachloop and configure the data flow task as required. For OLEDB Source inside the DFT, you need to choose the Data Access Mode as SQL Command From Variable and select variable SQL from drop down box.


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Kunal Joshi Monday, February 22, 2010 5:12 AM
    • Marked as answer by Zongqing Li Monday, March 1, 2010 6:31 AM
    Monday, February 22, 2010 5:01 AM

All replies

  • I think you need to put the data in text file into a table and then use an Execute SQL task to fetch the result into an object variable (objtest). Create 3 variables Col1,Col2 and Col3.Then take a for each loop contaioner with ForEachADOEnumerator as Collection and select the variable objtest from the drop down box. Then go to variable mappings inside the ForEachLoop Editor and do the mappings as:    Variable      Index
        ----------------------------
            User::Col1     0
            User::Col2     1
            User::Col3     2

    Create a Variable SQL with data type as string and set its "Evaluate as Expression" property as true. Then go to its expression property and set the expression as :
    "Select * From TableName Where Col1 = " +   @[User::Col1] + " And Col2='" +  @[User::Col2] + "'" + " And Col3='" +  @[User::Col3] + "'"    
    Take a DFT inside the Foreachloop and configure the data flow task as required. For OLEDB Source inside the DFT, you need to choose the Data Access Mode as SQL Command From Variable and select variable SQL from drop down box.


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Kunal Joshi Monday, February 22, 2010 5:12 AM
    • Marked as answer by Zongqing Li Monday, March 1, 2010 6:31 AM
    Monday, February 22, 2010 5:01 AM
  • Thanks Nitesh. I fixed the issue with your solution.
    sql
    Wednesday, February 24, 2010 4:42 PM