BartekR profile pic

BartekR

SQL Server. SSIS. PowerShell. Azure.
1 wife. 1 kid. 5 dogs. 10 cats.

One of the ssisUnit commands is a PropertyCommand. It allows you to read or set a property of the task, the package or the project. As of the time of writing - you can’t test the properties of the precedence constraints or data flow elements (but you can’t currently test data flow at all).

How do you use it?

Empty property command

The command is simple. You can get or set the property using the value for given property path. As usual - when you get the value, you leave the value blank. The path - well - is the path to the element in the package or the project. You use backslashes to separate elements in the package tree, and at the end, you use .Properties[PropertyName] to read the property. If you use the elements collection - like connection managers - you can pick a single element using square brackets and the name of this element.

When you take a look into the source code (\ssisUnit\SSISUnit\PropertyCommand.cs), you will see the examples like:

\Project\ConnectionManagers[localhost.AdventureWorks2012.conmgr].Properties[ConnectionString]
\Package.Properties[CreationDate]
\Package.Connections[localhost.AdventureWorksDW2008].Properties[Description]
\Package.EventHandlers[OnError].Properties[Description]
\Package\Sequence Container\Script Task.Properties[Description]
\Package.EventHandlers[OnError].Variables[System::Cancel].Properties[Value]

They all have one thing in common - they start with a backslash. It’s not required though, it’s a convention. I wrote that the PropertyPath uses the backslashes as the element separator. In the examples you see, that backslash is used interchangeably with a dot. In fact, it does not matter - during the parsing phase, all the backslashes are converted to dots.

How do you write the path? You can take a look at the package and name each part that you need to walk through, to get to your element. Or simply - when you take a look at any SSIS package source code, you will see, that each DTS:Executable has the DTS:refId attribute, that contains the path to the element in the package. You can safely use it as a path for the PropertyPath, or you can add a leading backslash.

In what scenarios you can use the PropertyCommand?

  • checking your programming standard (like verifying if DelayValidation always turned off or if the elements have a description other than the default)
  • overwriting your project connection manager connection string (if you want to run the tests on the different server)
  • automated testing of DFT buffer (you could run few tests with different values of DefaultBufferMaxRows and/or DefaultBufferSize and check the loading times)

Personally, up to now, I used only the project connection manager overwriting. It wasn’t available until the recent ssisUnit update, but now you can use it in your projects.

Recent Posts

Categories

About

Posts about SQL Server, SSIS and automation for my future self, but you might find something useful.