none
SQL Query using a Variable in Data Flow Task

    Question

  • I have a Data Flow task that I created. THe source query is in this file "LPSreason.sql" and is stored in a shared drive such as

    \\servername\scripts\LPSreason.sql

    How can I use this .sql file as a SOURCE in my Data Flow task? I guess I can use SQL Command as Access Mode. But not sure how to do that?

    Thursday, May 15, 2014 9:03 PM

Answers

  • Hi Desigal59,

    You can use a Flat File Source adapter to get the query statement from the .sql file. When creating the Flat File Connection Manager, set the Row delimiter to a character that won’t be in the SQL statement such as “Vertical Bar {|}”. In this way, the Flat File Source outputs only one row with one column. If necessary, you can set the data type of the column from DT_STR to DT_TEXT so that the Flat File Source can handle SQL statement which has more than 8000 characters.

    After that, connect the Flat File Source to a Recordset Destination, so that we store the column to a SSIS object variable (supposing the variable name is varQuery).

    In the Control Flow, we can use one of the following two methods to pass the value of the Object type variable varQuery to a String type variable QueryStr which can be used in an OLE DB Source directly.

    Method 1: via Script Task

    1. Add a Script Task under the Data Flow Task and connect them.
    2. Add User::varQuery as ReadOnlyVariables, User::QueryStr as ReadWriteVariables
    3. Edit the script as follows:
       public void Main()
            {
                // TODO: Add your code here
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
                DataTable dt = new DataTable();
                da.Fill(dt, Dts.Variables["User::varQuery"].Value);
                Dts.Variables["QueryStr2"].Value = dt.Rows[0].ItemArray[0];  
                Dts.TaskResult = (int)ScriptResults.Success;
            }

    4. Add another Data Folw Task under the Script Task, and join them. In the Data Flow Task, add an OLE DB Source, set its Data access mode to “SQL command from variable”, and select the variable User::QueryStr.

    Method 2: via Foreach Loop Container

    1. Add a Foreach Loop Container under the Data Flow Task, and join them.
    2. Set the enumerator of the Foreach Loop Container to Foreach ADO Enumerator, and select the ADO object source variable as User::varQuery.
    3. In the Variable Mappings tab, map the collection value of the Script Task to User::QueryStr, and Index to 0.
    4. Inside the Foreach Loop Container, add a Data Flow Task like step 4 in method 1.

    Regards,


    Mike Yin
    TechNet Community Support


    Friday, May 23, 2014 3:15 AM
    Moderator

All replies

  • Probably need to bring it in through the control flow and populate to a variable that the DFT can use
    Thursday, May 15, 2014 9:28 PM
  • Or even If I copy the ACTUAL SQL From that .sql file. I can I use the SQL, as a variable in DFD?

    Where do i specify the ACTUAL SQL CODE in the variable?

    Thursday, May 15, 2014 9:52 PM
  • Yes, you can, define a variable, paste the code in to the "Value" field and then use that in the source stage (choose the query from a variable option - does not resemble actual statement...)
    Thursday, May 15, 2014 9:58 PM
  • If I need to change the query, I will have to update the package. So how can I put this variable then in a Expression/config?

    Thursday, May 15, 2014 10:08 PM
  • Depends what you need, on your version, how your project is set up, design preferences etc I guess.

    How will the query change from run to run? Can the differences be programmatically determined?

    Thursday, May 15, 2014 10:40 PM
  • Hi ,

      Please try if this helps

    
    i.	Create an “Configuration Entry - for the Shared path Of a File”
    ii.	Create two SSIS Variables 
    a.	Var1_SharedPath  :  < this reads from “Configuration Entry” and Stores >
    b.	Var2_SQL_Str         : <this reads the Contents of a file  from Var1_SharedPath>
    The a & b operations can be done using C# based ScriptTasks 
    iii.	Use the Source to Read from the Variable “Var2_SQL_Str “ in SQL Command

    

     

    Thursday, May 15, 2014 11:19 PM
  • Thanks for the above reply. How can I read a & b above using the Script tasks? Can you pl provde the commands?
    Friday, May 16, 2014 2:22 PM
  • Hi Desigal59,

    You can use a Flat File Source adapter to get the query statement from the .sql file. When creating the Flat File Connection Manager, set the Row delimiter to a character that won’t be in the SQL statement such as “Vertical Bar {|}”. In this way, the Flat File Source outputs only one row with one column. If necessary, you can set the data type of the column from DT_STR to DT_TEXT so that the Flat File Source can handle SQL statement which has more than 8000 characters.

    After that, connect the Flat File Source to a Recordset Destination, so that we store the column to a SSIS object variable (supposing the variable name is varQuery).

    In the Control Flow, we can use one of the following two methods to pass the value of the Object type variable varQuery to a String type variable QueryStr which can be used in an OLE DB Source directly.

    Method 1: via Script Task

    1. Add a Script Task under the Data Flow Task and connect them.
    2. Add User::varQuery as ReadOnlyVariables, User::QueryStr as ReadWriteVariables
    3. Edit the script as follows:
       public void Main()
            {
                // TODO: Add your code here
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
                DataTable dt = new DataTable();
                da.Fill(dt, Dts.Variables["User::varQuery"].Value);
                Dts.Variables["QueryStr2"].Value = dt.Rows[0].ItemArray[0];  
                Dts.TaskResult = (int)ScriptResults.Success;
            }

    4. Add another Data Folw Task under the Script Task, and join them. In the Data Flow Task, add an OLE DB Source, set its Data access mode to “SQL command from variable”, and select the variable User::QueryStr.

    Method 2: via Foreach Loop Container

    1. Add a Foreach Loop Container under the Data Flow Task, and join them.
    2. Set the enumerator of the Foreach Loop Container to Foreach ADO Enumerator, and select the ADO object source variable as User::varQuery.
    3. In the Variable Mappings tab, map the collection value of the Script Task to User::QueryStr, and Index to 0.
    4. Inside the Foreach Loop Container, add a Data Flow Task like step 4 in method 1.

    Regards,


    Mike Yin
    TechNet Community Support


    Friday, May 23, 2014 3:15 AM
    Moderator
  • Thanks for the above reply. How can I read a & b above using the Script tasks? Can you pl provde the commands?

    If you're going to have a fixed string as the SQL query, I would suggest holding in a table and pull in via a SQL Task rather than hold in a file. But as mentioned previously, all depends on what your requirement.
    Friday, May 23, 2014 3:48 AM
  • I put the entire Code in a SQL Table. then i use Excecute SQL Task and assign this query to a variable. THen in DFD Task I am trying to use this Data Access Mode as TABLENAME or Variable.  In the value of the variable I put the entire code, the code is a join of several tables.

    However I get an error that "the table/object doesnot exsit. Check that the object exists in the db". however the table does not directly exist, but it is based on several joins. How do I proceed with assigning this query code to a variable?

    Monday, July 14, 2014 10:10 PM