'Auto Layout - Diagram' missing in Layout Toolbar

Case
I often use the 'Auto Layout - Diagram' option in the Format-menu to auto arrange my tasks or transformations, but it costs me three clicks (/moves). All other options from the Format-menu can be found in the Layout toolbar (one click only), except the Auto Layout - Diagram option. Is there a way to solve that for 'lazy' developers?

Three clicks instead of one

Solution
Yes there is! Screens are from VS2010, but it works the same in newer versions.

1) Show Layout toolbar
First make sure the Layout toolbar is visible. If it's not visible, rightclick the toolbar and select the Layout toolbar.

Show Layout toolbar

2) Add Button
Click on the little triangle on the right site of the toolbar and choose 'Add or Remove Buttons'. After that choose 'Customize...'. Now the Toolbar Customize window will show.

Customize window

3) Add Command
Click on the Add Command button and choose Format as category and then locate the Diagram command. Click OK to add it and click Close to close the customize window.

Add command

4) The Result
Now the new button is available in the Layout toolbar and with one click you can auto arrange your package.

One click only :-)

Foreach loop with *.xls wildcard also returns *.xlsx files

Case
I have a Foreach Loop Container with a file enumerator. The wildcard is *.xls, but it also returns *.xlsx files. How do I prevent that?


Loop through *.xls also includes xlsx files

My xls loop includes xlsx and xlsm files

Solution
This is actually similar to the DIR command in a DOS/Command Prompt.

All xls files? (/b is to remove my Dutch header/footer)

The workaround is simple. And if you don't like the solution then you could use my Sorted File Enumerator that also supports regular expression wildcards.

1) Dummy
Add an empty/dummy task or Sequence Container in your Foreach Loop Container. And connect it to your first task.


Empty/collapsed Sequence Container added

2) Precedence Constraint Expression
Add an expression on the Precedence Constraint between the dummy and your first task. It should look something like LOWER(RIGHT(@[User::FilePath], 4)) == ".xls" (replace the variablename and/or file extension).


Expression with LOWER and RIGHT to check the file extension

3) The result
Now test the package (Replace my example Script Task with your own tasks).

The result: only two xls files and no xlsx or xlsm files

XSD location hardcoded in XML source

Case
The path of the XSD file in the XML Source component is hardcoded, but the path on my production environment is different than my development environment. The XML source doesn't have expressions. How do I make this XSD path configurable?

XML Source Connection Manager Page
without Connection Managers

Solution
Unfortunately the XML source component does lack some very basic functionalities like the use of a Connection Manager (although ironically it's called the Connection Manager page). The source can use variables as input, but there isn't such option for the XSD file.

XML Task can use Connection Managers

1) Find Expressions
The XML Source Component doesn't support expressions, but the Data Flow Task itself does. Go to the properties of the Data Flow Task and locate the expressions and click on the ... button


Select Data Flow Task and press F4 to get properties

2) Select Property
Locate the XMLSchemaDefinition property of your XML Source in the Property Expression Editor and add an expression on it by clicking on the ... button.

Property Expression Editor

3) Add Expression
Now you can either replace its value by a variable or a parameter if you use SSIS 2012 and above.

Expression Builder

That's it. Now you have a workaround for the absence of a real Connection Manager. An alternative could be to use a Script Component as XML source or an XML file with an inline XSD schema.