0

I have and SQL Execute task that gets me a list of years and save them into a an object variable, let's say "Years". Then I have a ForEach Loop Counter that recieves thah variable, and inside that Counter I have a DataFlow task, what I want to do is use that "Years" variable in the Dataflow task as I need to use that list of years in my query.

Any suggestions on how to do that? Oh my query in my DataFlow task is an MDX query.

Thanks

user1112251
  • 119
  • 1
  • 4
  • 13
  • Execute SQL task load a variable, Years. You shred that object with a ForEach loop and assign the current value to a second variable, CurrentYear. The question is how can you use the CurrentYear (primitive type) in the data flow or are you attempting to use Years (object type)? – billinkc Dec 22 '11 at 20:18
  • Since I want to use the value of the variable in an MDX query I think It would be easier to use the String. How can I use the foreach loop to assign the value to another variable and then use it in the Dataflow task? – user1112251 Dec 23 '11 at 18:34

1 Answers1

0

Since you need to build the MDX query dynamically and append the specific year in the counter to it, you need to:

  1. First create a new string variable called MDX_Query.
  2. Then esnure that the "Evaluate as expression" option is set to True
  3. Now create an expression for that variable that is basically your MDX query and append your @Years variable to it. Something alongs the lines of
    "MDX query text goes here where SalesYear.["+ @[User::Years_Variable]+"]"
  4. Now inside your Foreach Loop task, if you have created a SQL query task, ensure that your "SQL Source Type" is set to variable and set the "Source Variable" to MDX_Query
Casual Pundit
  • 279
  • 2
  • 10