CODEWITHSUNDEEP
sql-serverssis
Metaphor
Metaphor

Reputation: 6415

How can I use a variable in a data source?

I am transferring a very large table that has a column called EndOfSessionTime and I would like to get a var LastSess=MAX(EndOfSessionTime) from the destination and transfer only rows WHERE EndOfSessionTime > LastSess.

I set the variable using a ScriptTask in the control flow, but I can't seem to find a straight-forward way to use the variable in a data source.

Upvotes: 0

Views: 364

Answers (2)

Metaphor
Metaphor

Reputation: 6415

To use connection parameters is a must in SSIS, but I had a heck of a time to find how. Microsoft made up a word "Parametrization" and using it as a search term seems to be the only way to come up with how.

The connection manager names at the bottom of the SSIS desktop are actual objects. Right-clicking on one and selecting "Parametrization" is how connection parameters are set. One more note on that: There is no "Apply" on this dialog, so set one paramter at a time, OK, then right-click-Parametrize again. If you set a parameter and go on to the next one without clicking OK, the first one will be lost.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

2 options:

1) Stuff your entire SQL Query into a variable, and in the OLEDB Data Source, choose "SQL Command From a Variable"

2) Use parameters in your SQL query. Indicate a placeholder for a parameter with a Question Mark character, and then hop over to the Parameters tab to assign the variable to the parameter. Google "SSIS Data Source Parameters" for tutorials and examples.

Upvotes: 1

Related Questions