Friday, 12 September 2014

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





















Monday, 1 September 2014

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.
Related Posts Plugin for WordPress, Blogger...