Categories: SSIS

Reblog: Unexpected result with the File System Task

SQLKover update: nowadays you would probably use package parameters to pass the directory value. However, they still have a default value, so this still applies.

Recently I ran across a forum thread where someone encountered an unexpected result when creating a directory using the File System Task in Integration Services. He used a variable to specify the name of the directory to be created and this variable was populated by a parent package configuration. If the configuration failed, he didn’t want the package to create the directory specified during design time, so he entered “invalid” in the variable value, expecting the package to fail as it is not a correct path to a directory. This is a common (best) practice to make sure the package doesn’t change your development environment if the configuration in production fails. If you get for example an error from a flat file source saying: “cannot find file invalid”, you immediately know the problem lies with the package configurations.

However, the package did not fail. Instead, it created a directory called Invalid. I recreated the set-up using this simple package:

When I run the package using SSIS 2012 in Windows 8, the folder HelloWorld!!! Is created in the same folder where the SSIS package is stored. On another computer – using SSIS 2008R2 in Windows XP – the folder is created in the My Documents folder. The guy from the forum thread mentioned his folder was created on his desktop. The documentation doesn’t mention where a directory is created when there is no absolute path specified and a quick Internet search didn’t yield an explanation either. It’s also unclear if the choice where the directory is finally created is influenced by the version of SSIS, the OS version (an environment variable?) or some obscure setting no one knows about.

Conclusion: very unpredictable results. The directory might still be created and who knows where all your files end up. Better avoid this way of working with a file system task. If you really want to validate directory paths and handle exceptions, I recommend a script task.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

View Comments

  • There are differences between XP and win7 onwards for the users default directory.
    For example on win xp the user directory is located at

    C:\documents and settings\username\

    On win7 upwards it will be

    C:\users\username\

    The users default directory is likely different too but I haven't checked to confirm. The task seems to check if the folder exists if not it just creates it. I believe setting UseIfDirectoryExists to false will produce a failure otherwise as we see the object is simply created if it doesn't exist
    Regards perry

Recent Posts

Call a Fabric REST API from Azure Data Factory

Suppose you want to call a certain Microsoft Fabric REST API endpoint from Azure Data…

11 hours ago

Cool Stuff in Snowflake – Part 14: Asynchronous Execution of SQL Statements

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse).…

2 weeks ago

How I passed the DP-700 Exam

I recently took and passed the DP-700 exam, which is required for the Microsoft Certified:…

4 weeks ago

Take over Ownership in Microsoft Fabric

When you create an item in Microsoft Fabric (a notebook, a lakehouse, a warehouse, a…

2 months ago

Book Review – Agile Data Warehouse Design

I recently read the book Agile Data Warehouse Design - Collaborative Dimensional Modeling, from Whiteboard…

3 months ago

Cloudbrew 2024 – Slides

You can find the slides for the session Building the €100 data warehouse with the…

4 months ago