2 Replies Latest reply on Jun 8, 2012 9:41 AM by 803169

    Using a variable inside another variable

    803169
      I'm trying to use one two variable in a package with one variable feeding its value to another variable as follows:

      Variable 1 = Count
      Variable 2 = File_Name

      Variable 1 counts up from 1;I would like to use this value as an input to Variable 2 as follows:

      SELECT LOAD_FILENAME FROM DIMSTATS_FILENAME WHERE (SELECT ROW_NUMBER() OVER (Order by FILE_ID) AS RN FROM DIMSTATS_FILENAME) = #DIMSTATS_TEST.count.

      How ever I am getting the following Error :

      +ODI-1228: Task File_Name (Variable) fails on the target  connection DDBSTAGE.
      Caused By: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]The multi-part identifier "#DIMSTATS_TEST.count" could not be bound.+

      I have also noticed that the value of Variable 1 is not being passed to the Select statement above through SQLServer Profiler. If I hard-code the value of #DIMSTATS_TEST.count (Variable1) the query is successful.

      How can I get the value of Variable 1 to be passed to Variable 2?

      Could someone please help

      Thanks