Friday, May 8, 2015

Powershell copyhere Behavior Differs between Integrated Development Environment and Integration Services Catalog

Quick Description:  in Powershell, shell.application.copyhere works in a script called by SSIS when executed from SQL Server Data Tools.  It does not work when executed from the SSIS catalog when deployed to SSIS/SQL Server.

Do not try to use shell.application copyhere as a way of unzipping a file in a powershell script called by SSIS.  It will work in the IDE, it will work executing from the Integration services catalog, and it will fail spectacularly as a sqlAgent job.  Apparently, copyhere only works when called as a script for regular files, and only works for zip files when it feels like it (I'd argue that executing from the SSIS catalog isn't exactly interactive, but apparently, I'd be wrong).

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.