I've used to Parameter Editor to set two user variables I want to use in my data flow. (&INDATE, &OUTDATE).

I want to use these variables in my data flow as a filter. However, when I get to the filter, only DM Variables and System Variables appears as valid options. User Variables isn't even listed under "Variables" in the filter calculator.

I have the Set Variables icon between my Start and Data flow icons, and they are attached.

What am I missing? Why can't I see these variables I've created in the SQL Filter?

Thanks!

The variables in the process flow are assigned values at run time. You can type them into the filter, with enclosing single quotes for character or date values. When you test the SQL you will be prompted for a value, but when you submit the flow the value you assigned gets used.

Thanks Clif - my problem is I need to calculate the date on the fly to determine which table to access. Entering a hardcoded date for each monthly run isn't an option.

We have 25 months of data under a view, and they are split out by AS_OF_DT, which is the first day of the month.

So in June, I want to grab the data where AS_OF_DT = '2011/05/01' and move it to another table. In July, I want the data where AS_OF_DT = '2011/06/01'.

Since this will be in a schedule, and in production (where I can't modify the code) entering a value each time this runs isn't feasible. While I can create DEFINE statements for these variables, it is then calculated for every record read, rather than just once when the job kicks off and seems to really add to the run time.

I do have a backup plan which is to calculate the date via another job and put it into a 1 row table I can then join to, but that's kind of clunky. It works, but it would be a lot cleaner to be able to use a variable in the filter.

I don't suppose there is any way to get variables added to the "DM Variables" or "System Variables" that are available to be used in the filter?

OK - I got it working now. Probably a combination of things I was doing, but once I viewed it through the text editor I could see how it was flowing and was able to make it work.

Thanks!