I’ve used the File System task before, but it was to do a daily copy of files in a directory. I used copy directory so I didn’t have to specify or loop through any files. It was the same process every time and the destination files could be replaced, so I used static variables for Source and Destination and everything worked fine.
This time I’m moving one file and renaming it so it’ll be unique and I just couldn’t get it to work. I was trying to use expressions to concatinate the standard file name and an integer. It worked just fine when I’d click “Evaluate Expression”, but would fail every time I’d try to run the step. I’d get a typical, always unhelpful error from SSIS
Failed to Lock Variable
The more verbose explanation in the message held the clue though. It told me that the variable “C:\OUT\Access\filename.accdb” could not be found. What? I didn’t have a variable by that name, that should be the contents of the variable. What the hell was going on?
Another clue was in the various instructions I found on using File System Task on-line and in books.
Set the variable’s EvaluateAsExpression property to true.
That inspired me to go through the various properties variables have in SSIS. Right under EvaluateAsExpression it says Expression.
At this point, I decided to start again from scratch. I didn’t want to take a chance that anything I’d already done wouldn’t change properly. SSIS has burned me that way in the past and that’ll be a future post.
Here’s how I’d been setting the expression so far. I created the variables, set IsSourveVariable to TRUE, IsDestinationVariable to TRUE and selected the variable for source and the variable I thought would hold the evaluated expression as destination. Then I went to Expressions in File System Task and created the appropriate expression for the Property Destination. When I ran it, it replaced the variable in Destination with the string I wanted to be the destination.
This time I didn’t touch Expressions in the File System Task. When I created the Destination variable, I went to the variable’s properties, set EvaluateAsExpression as TRUE and then created the concatination expression right there in the variable’s Expression property.
After I had the variables set up I went into the File System Task and set the variables as before, but didn’t touch Expressions within the File System Task. I ran the task and it worked beautifully. I initially tried it as a rename process, but that moved the file and didn’t leave the original. I wanted to leave the original so I changed it to copy and that worked too. Apparently it was all in where I create the expression.
I thought that perhaps I was just assigning the variable incorrectly. Up to this point I’d been doing everything with User:: variables. When I’d created the expression within File System Task, it asked for a Property name. I figured I should look for a System:: variable, but there isn’t one in the list. I think there must be some way to access that property for the Destination field, but I haven’t been able to find it as yet.
That aside, I’m satisfied with how it is working now. Next step is to set one of the variables going into the expression as the result of a query. We’ll see how that goes.