Thursday, May 7, 2015

SSIS Attunity Oracle Source SQL Command from Expression Difficulties

Short Description:  When attempting to use a variable/expression to populate the SQLCommand, the expressions property is not showing up when looking at source properties.

The post title is a mouthful, but the upshot is that I ran into the unfortunate situation in which, when attempting to use a variable as the SQL Command for an Attunity Oracle source, the expressions menu wasn't showing up in the Oracle Source properties.

I'm not sure if this is always the case.  It certainly seems that the expectation is that the expressions property will show up under 'Misc Properties' per a number of posts like this one If you're not specifically having trouble setting a variable as the SQL Command because the expression properties are not available, you may want to start with this post, it will answer the basic questions around setting the query from an expression.

Solution:  If you're stumped because you're used to setting variable expressions as the query for data sources, but you can't find where to enter the expression, then we have/had the same problem.  Right clicking the Oracle Source and combing through the properties comes up blank for misc properties and the expressions property in particular.

In this case, to get to the expressions property of the source,

  • Right click the working surface of the data flow task 
  • Click on the ... icon in the Expressions property of the data flow task.
  • The Property Expressions editor will come up
  • Click the drop-down arrow in the Property drop down
  • Select the [Oracle Source].[SQLCommand] property, and enter your variable.
I know...this would be easier to follow with screenshots.  I hope to pull some together when I have a chance.

1 comment: