What Is It For?
If you've ever needed to know if a specific file exists, whether you'll be able to open it to read or write to it, or what some of the date or file attribute properties of it are, you probably resorted to using a Script Task and the System.IO namespace. If you were a little more resourceful, you probably tracked down the File Properties task that Brian Knight of Pragmatic Works developed. Scripts work well - except for the reusability and opaqueness aspect, and the fact that you need to know the System.IO .Net namespace to get anything done. The Pragmatic Works task is fine too - except it's only available for SSIS 2005.
If you need more than the above can provide, hopefully this Task can come to the rescue.
What Does It Do?
The task will read properties from a file you specify as a literal string, using property expressions, or from an SSIS variable. You can fail the task if the file doesn't exist, or you can record whether or not it exists in an SSIS boolean-typed variable. If it does exist, you can read all kinds of properties off the file into appropriately-typed SSIS variables - perfect for conditionally controlling your package execution with Precedence Constraints set to Expression and Constraint.
Here's a sample view of the Task editor (click to enlarge):
How Do I Get This?
The Task is available for SQL Server Integration Services 2005 and 2008 from here.
If you have any difficulties at all, post in the Project Discussion area.
Can I See How It Works?
You bet! The entire Task source including the installer projects are on CodePlex. Download the source, tweak it to add some more capabilities, and then upload a patch back to CodePlex to share your new feature.
Hi, I have used your File properties task and it's very powerfull and user friendly (it cuts out a lot of script task) but I'm not able to use it, it doesn't recognize my package variable (I have sql server 2008). Do I have to do something to setup variables?
ReplyDeleteThanks, Francesco
You probably have an incompatible type specified for a variable. The file existence, readable, and writable variables need to be Boolean, the file length needs to be an Int64 or UInt64, and the creation, access, and modified dates need to be DateTime.
ReplyDeleteBut hey! I did just find that the attributes weren't looking at the right type of variable - if that's what you were trying to do, check the project site shortly, I'll have a patch up.
Let me know if those suggestions or patch doesn't address your issue.
Yes you are right, I have used wrong variables. I have downloaded the last release, correct variable type and now all works!
ReplyDeleteThanks, your application is very usefull!
Hi Todd,
ReplyDeleteThank you for creating the File Properties Task. It worked great on my laptop. But it does not work on the server. When dragged into a Foreach Loop Container, right clicked, and edit the following error occurs:
The task with the name "File Properties Task" and the creation name "ToddMcDermid.SSIS.FilePropertiesTask, FilePropertiesTask90, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6c005605b216cf47" is not registered for use on this computer.
But the following error occurs when going to start, run, regsvr32 "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\FilePropertiesTask90.dll" and clicking ok to register it:
The module "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\FilePropertiesTask90.dll" was loaded but the entry-point DllRegisterServer was not found. Make sure that "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\FilePropertiesTask90.dll" is a valid DLL or OCX file and then try again.
You're welcome. Please refer to http://filepropertiestask.codeplex.com/Thread/View.aspx?ThreadId=69030 for information on fixing the problem.
ReplyDeleteHello Todd
ReplyDeleteAwsome code. Works great on my personal laptop. Have it loaded on my work pc and not the server.
[File Properties Task] Error: Unable to retrieve variable information: Value does not fall within the expected range.
Is the error O receive.
Mark
Hi Mark,
ReplyDeleteI'll need some more details from you in order to try to find the issue. Can you post more information over at CodePlex, by creating a new Issue for the project? Please let me know in there how you have the task configured - what properties you've set and so on.
Thanks!
Ok DOne id #6605
ReplyDeleteMark
Hi Todd
ReplyDeleteDoes the File Properties Task work in SSIS 2008 64 Bit?
Thanks
Gary
@Anon - It should just fine.
ReplyDeleteIt works fine for me in 2008, but recently we got upgraded to 2012. and its not working anymore. Any help ? Will it run successfully on 2012 ?
ReplyDeleteNone of the Tasks I've made on CodePlex has been updated for 2012 (yet) - I hope to get to them soon, but don't take that as a promise. I've had one offer of assistance to update the DMSCD to 2012, but don't know if that patch is up yet.
ReplyDeleteTodd:
ReplyDeleteFirst of all...great component!!! I'm not a scripter so this came in very handy!
One thing I've run into is setting this up with a precedence constraint. I have the FileProperties task read the size of the file into a variable called FileSize. I have two precedence constraints configured using expressions. One expression is @FileSize <= 1 - meaning if the file size is equal to or less than 1KB in size, do X - which is to execute a Send Mail task alerting to an empty file. The second precedence constraint is an expression of @FileSize > 1, which directs the package to complete its normal processing. For some reason, the package only executs the Send MAil task, even if the file is greater than 1KB. I've got a file that's 10MB and it still evaluates the expression to 1 or less.
Am I missing something?
Todd:
ReplyDeleteI was actually able to figure out the issues I was having.
First, the expression was goofy. Should be @[User::FileSize] versus what I had. Even though I changed the expression, it gave me a new error - the "must evaluate to True or False" thing.
Turns out the scope of the variable you create to hold the file size needs to be at a package level and not just the task level. Took me a while to figure that one out.
Once I did, everything ran fine!
Thanks again for an awesome tool!
You're welcome, Ansonee - good to see you found your problem. Variable scoping isn't all that obvious!
DeleteOne other thing for you to make sure you're aware of - the file size returned in that variable is in bytes - so if you want to have your expression test for 1KB or less, it should be @FileSize <= 1024. (BTW - the @[User:: prefix isn't absolutely necessary - but it's harmless to keep in there.)
Yup!!! I realized the whole thing with the bytes vs kilobytes!!
DeleteAgain...thanks for the help and the handy dandy tool!!
I use this to detect file changes and then import the filenames into an database. However it doesnot work. I load the modified date into an variable (scope package) but when i write it to an database table the creation date is used instead.
ReplyDeleteOn the properties screen the items Parse File Name Into and Construct Full file Pathname into I select an unique variable. but when i reopen the properties screen later the values show the same variable. the one i assigned to Parse File.
Can you help me with this ?
Hello Todd,
ReplyDeleteJust curious as to any updates to this component or if there will be. Its 2018 now of course and I realize this is a long time add in component that would be hard to keep up with.
There are not yet... and I don't want to get your hopes up - but this is actually the one task I'm looking to use again, and I'm in SQL 2016 where I'm at. So... maybe soon? I will likely take another look at CodePlex and that they're retiring it and all that and see what that means...
Delete