none
SSIS - OLEDB Connection with "SQL command from variable" problem

    Question

  • Hi All,

    Here's what I am trying to do - Use a variable that is evaluated on a foreachloop container (the variable is populated and evaluated from the loop i.e. @[User::Conn] is evaluated to "SELECT " +@[User::Keys]+" FROM "+@[User::Table]) as a connection string for a dataflow task utilising an OLE Db connection. I have set up the connection with a dummy connection string variable, just to get it to work initially, and then changed the 'validate external metatdata' to FALSE and updated the connection to the real variable used for the connection.

    When I run this I get the following error on the DataFlow Task:- [SSIS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202005. 

    I want to loop through the tables, using the columns provided so that I can assess if there have been any deleted records from an identical table the day before (delta changes). I was going to use the lookup transformation to do this afterwards.

    Any ideas why this is not working or if it can work? The evaluated connection string would look like ...SELECT id FROM  test

    Thanks in advance

    E.


    DBA

    Tuesday, April 10, 2012 9:49 AM

All replies

  • Hi,

    What you are doing is use one DFT for each table or set of tables in the DB. This is not possible as Data Flow task works on static metadata and there would be no scenario where differrent tables have the same meta. Hence yo are getting the error.

    You would have to create the package dynamically or have separate DFT for each table.


    My Blog    |      Ask Me     |      SSIS Basics     

    Tuesday, April 10, 2012 10:06 AM
  • Ideally what ever expression you are mentioning should be set as the source for the  source task within the Data flow task. What ever you returning is not a connection string It is a source query for the source task. Please change the expression and try.

    If you are using a OLDDB source  , To use this variable value as source , you can use the "SQL Command from variable " option with in the OLEDB source .

    Tuesday, April 10, 2012 10:11 AM
  • Hi Sudeep, 

    Am I not using a new DFT for each table as the DFT is being called within the foreachloop container?

    If this is not the case - Any pointers on how to create the package dynamically?

    Thanks in advance

    E


    DBA

    Tuesday, April 10, 2012 10:13 AM
  • Hi Ness,

    It might seem to you that you are creating new DFT for each iteration of foreach loop. But what SSIS does is sets up the DFT for a specific meta data, and this DFT is referenced again and again, hence the Meta data changes are not allowed. The rational behind this is that the DFT can have other transformations as well, what if a column is used for a transformation and that column is not available in the next iteration, this will cause the DFT to fail. hope this makes sense.

    To create the Pacakge dynamically you need to write code and here is a guide.

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64572/

    http://consultingblogs.emc.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx


    My Blog    |      Ask Me     |      SSIS Basics     

    Tuesday, April 10, 2012 10:20 AM
  • That is what I am doing (sorry incorrect usage of connection, I meant the source for the OLEDB Con). The select from the variable is being used as the source of the OLEDB source. This should work right?

    DBA



    • Edited by Ness22 Tuesday, April 10, 2012 10:22 AM
    Tuesday, April 10, 2012 10:20 AM
  • That is what I am doing (sorry incorrect usage of connection, I meant the source for the OLEDB Con). The select from the variable is being used as the source of the OLEDB source. This should work right?

    DBA

    This will not work as I explained earlier.

    My Blog    |      Ask Me     |      SSIS Basics     

    Tuesday, April 10, 2012 10:32 AM