September 8, 2010 at 11:53 am
Okay, I'm stumped and my google-fu does not work so well today. How do you make the Source and Destination expressions in a File System Task work properly?
Simple SSIS with 5 tasks. 3 main variables. DBFileName, FileDirectory, ZipFileName. All strings.
A FOR EACH LOOP container uses the FileDirectory variable as its Enumerator Folder and populates ZipFileName with the name and extension of the file listed in Enumerator Files. Since each zip file usually has a date appended, I had to code this as a wild card, hence the need for the second variable.
Inside the container, an Excecute Process runs Winzip and pulls a MS Access db out of the .zip file. Since the db can be a different name each month (structure is the same though), an Execute SQL Task does a directory pull into a temp file and populates DBFileName.
Data Flow Task to upload the data to SQL Server. This is the easy part.
File System Task to move the zip file from my Inbound folder to my Archive folder. The Inbound folder is the path saved in FileDirectory.
What I'd like to do is use the expressions for the FST to populate the destination and the source. I can't seem to make it work, though. Source expression is @[User::FileDirectory] + @[User::ZipFileName] and Destination expression is REPLACE(@[User::FileDirectory],"Inbound","Archive") + @[User::ZipFileName].
I don't want to create new connection managers for this, which is what FST forces me to do if I have IsDestinationPathVariable set to False (or Source). But if I set it to True, it won't evaluate the expressions properly. The errors I'm receiving are:
SSIS Error
Error: 0xC002F304 at Archive Zip File, File System Task: An error occurred with the following error message: "Failed to lock variable "\\Server\MyPath\Inbound\MyFile_20100901.zip" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".".
All tasks using the variables have Delay Validation set to True.
Does anyone have any thoughts on what I'm doing wrong or weblinks I could go visit?
September 8, 2010 at 12:21 pm
It looks like it's trying to find a variable with the name "\\Server\MyPath\Inbound\MyFile_20100901.zip"
Did you happen to use the expression for the value of the variable where you should have used the variable's name?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 8, 2010 at 12:28 pm
Brandie Tarvin (9/8/2010)
Okay, I'm stumped and my google-fu does not work so well today. How do you make the Source and Destination expressions in a File System Task work properly?Simple SSIS with 5 tasks. 3 main variables. DBFileName, FileDirectory, ZipFileName. All strings.
A FOR EACH LOOP container uses the FileDirectory variable as its Enumerator Folder and populates ZipFileName with the name and extension of the file listed in Enumerator Files. Since each zip file usually has a date appended, I had to code this as a wild card, hence the need for the second variable.
Inside the container, an Excecute Process runs Winzip and pulls a MS Access db out of the .zip file. Since the db can be a different name each month (structure is the same though), an Execute SQL Task does a directory pull into a temp file and populates DBFileName.
Data Flow Task to upload the data to SQL Server. This is the easy part.
File System Task to move the zip file from my Inbound folder to my Archive folder. The Inbound folder is the path saved in FileDirectory.
What I'd like to do is use the expressions for the FST to populate the destination and the source. I can't seem to make it work, though. Source expression is @[User::FileDirectory] + @[User::ZipFileName] and Destination expression is REPLACE(@[User::FileDirectory],"Inbound","Archive") + @[User::ZipFileName].
I don't want to create new connection managers for this, which is what FST forces me to do if I have IsDestinationPathVariable set to False (or Source). But if I set it to True, it won't evaluate the expressions properly. The errors I'm receiving are:
SSIS Error
Error: 0xC002F304 at Archive Zip File, File System Task: An error occurred with the following error message: "Failed to lock variable "\\Server\MyPath\Inbound\MyFile_20100901.zip" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".".
All tasks using the variables have Delay Validation set to True.
Does anyone have any thoughts on what I'm doing wrong or weblinks I could go visit?
Have you tried making the Destination expression @[User::FileDirectoryB] + @[User::ZipFileName]?
SSIS errors are horrible things. Half the time they're not telling you what you need to know to fix them. It could be the REPLACE function is bogging things up.
I recently used the File System Task and found the problem I was having was in specifying the file name for Destination instead of just a Folder Name.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 9, 2010 at 4:17 am
Stefan, I've tried making the Destination just the folder name, but it wouldn't work. The errors are also complaining about the Source expression just being @[User::FileDirectory] + @[User::ZipFileName].
Alvin, If I create variables for the Source and Destination expressions, then I don't need expressions. All I have to do is use the variables within the general tab of the FST. And I don't want to create additional variables when the ones I have should do just as well.
If I have to create additional variables, then I have to create them. But if that's the case, why would SSIS have Expression capability for Source and Destination if they can't be used?
September 9, 2010 at 5:10 am
As a test, I created a new variable called ArchiveDest, set the Evaluate as Expression property to True and set the Expression to REPLACE(@[User::FileDirectory],"Inbound","Archive").
I removed the Destination expression, Made sure that IsDestinationPathVariable was True and used User::ArchiveDest as the DestinationVariable.
I also added a script task to give me a message box to pop up what the final Source path would be when using @[User::FileDirectory] + @[User::DBFileName] as the Source Expression.
While IsSourcePathVariable was True, I kept getting those "cannot lock variable" errors for the Source. The Destination errors disappeared. When I set IsSourcePathVariable False and left SourceConnection blank, I got:
Error: 0xC001000E at MyPackageName: The connection "\\MyServer\MyPath\Inbound\" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error: 0xC001000E at MyPackageName: The connection "\\MyServer\MyPath\Inbound\" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackageName.dtsx" starting.
Error: 0xC002F304 at Archive Zip File, File System Task: An error occurred with the following error message: "The connection "\\MyServer\MyPath\Inbound\Inbound\MyZipFile_20100901.zip" is not found. This error is thrown by Connections collection when the specific connection element is not found.
".
Error: 0xC0024107 at Archive Zip File: There were errors during task validation.
Warning: 0x80019002 at MyContainer Data: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackageName.dtsx" finished: Failure.
I really want to get expressions working so I don't have to create new variables. I'd like to just reuse the ones I have. @sigh. But apparently that's not in the cards.
September 9, 2010 at 5:33 am
More experimenting. When I create new variables and set the variables using Evaluate as Expression and putting the expressions there, then set the FST to those variables, the task works.
HOWEVER, if I change the Destination variable to @[User::FileDirectory] and then use the Expressions in the FST to put in the REPLACE(), the task fails again with the "unable to lock variable" error.
I found another thread here on SSC that indicates variables need to be set to Read Only in order to work with FST expressions, but unfortunately I can't set the variables to read only as I am writing to the variables earlier in SSIS. So, basically, this means that Expressions in FST are essentially broke and cannot be used effectively. At least, not in 2005.
Has anyone tried this in SSIS 2008? Perhaps they fixed the problem there? Or maybe it's my SP version. I'm still on SP1 + hotfix.
October 4, 2010 at 1:51 pm
Brandie Tarvin (9/9/2010)
More experimenting. When I create new variables and set the variables using Evaluate as Expression and putting the expressions there, then set the FST to those variables, the task works.HOWEVER, if I change the Destination variable to @[User::FileDirectory] and then use the Expressions in the FST to put in the REPLACE(), the task fails again with the "unable to lock variable" error.
I found another thread here on SSC that indicates variables need to be set to Read Only in order to work with FST expressions, but unfortunately I can't set the variables to read only as I am writing to the variables earlier in SSIS. So, basically, this means that Expressions in FST are essentially broke and cannot be used effectively. At least, not in 2005.
Has anyone tried this in SSIS 2008? Perhaps they fixed the problem there? Or maybe it's my SP version. I'm still on SP1 + hotfix.
I'd lost track of this thread awhile ago and found it again through googling now that I'm having the same problem. Imagine my surprise when I saw I was one of the respondants on a thread I was looking to for an answer.
Sadly, I still don't have one, but now that I think I see what is happening for you I wanted to ask a follow-up question. The reason I seem to be getting the "Failed to lock" error is the way SSIS is handling the variable. From what you posted in your initial post, it seems you're getting the same problem.
When you have the property Evaluate As Expression on your variable set to TRUE, it appears as though SSIS processes the expression to get the resulting string and then, since you have IsDestinationPathVariable set to True, it looks for the full output string in the Variables list.
I think this is a bug as I don't see any way to set the properties to use the expression in the variable instead of using the expression AS the variable. All the solutions I've seen so far involve using a script task to set the variable instead of using expressions. I'll likely try this next and if it works, I'll post the workaround here.
Any idea where I could report this as a bug so at least I can see if Microsoft will tell me it is a bug?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 4, 2010 at 1:55 pm
Alvin Ramard (9/8/2010)
It looks like it's trying to find a variable with the name "\\Server\MyPath\Inbound\MyFile_20100901.zip"Did you happen to use the expression for the value of the variable where you should have used the variable's name?
Alvin,
I'm running into the same problem. My expression is
@[User::SourceName] + @[User::FiscalWeek] + ".acccbd"
It evaluates just fine, but then SSIS seems to think the variable name is the string that results, not the variable @[User::CombinedName] that contains the expression.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 4, 2010 at 2:20 pm
Stefan Krzywicki (10/4/2010)
Alvin Ramard (9/8/2010)
It looks like it's trying to find a variable with the name "\\Server\MyPath\Inbound\MyFile_20100901.zip"Did you happen to use the expression for the value of the variable where you should have used the variable's name?
Alvin,
I'm running into the same problem. My expression is
@[User::SourceName] + @[User::FiscalWeek] + ".acccbd"
It evaluates just fine, but then SSIS seems to think the variable name is the string that results, not the variable @[User::CombinedName] that contains the expression.
What property or variable are you assigning that expresion to?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 4, 2010 at 2:27 pm
Brandie Tarvin (9/9/2010)
More experimenting. When I create new variables and set the variables using Evaluate as Expression and putting the expressions there, then set the FST to those variables, the task works.HOWEVER, if I change the Destination variable to @[User::FileDirectory] and then use the Expressions in the FST to put in the REPLACE(), the task fails again with the "unable to lock variable" error.
I found another thread here on SSC that indicates variables need to be set to Read Only in order to work with FST expressions, but unfortunately I can't set the variables to read only as I am writing to the variables earlier in SSIS. So, basically, this means that Expressions in FST are essentially broke and cannot be used effectively. At least, not in 2005.
Has anyone tried this in SSIS 2008? Perhaps they fixed the problem there? Or maybe it's my SP version. I'm still on SP1 + hotfix.
I think I figured it out!
I'm not sure how you're creating the expression, but if you did it the way I did it when I got the error, I was opening the File System Task and going to Expressions within the task.
As I often do when I'm getting frustrated with SSIS, I decided to start from scratch.
I created three variables at the package level
Source, Front, End
Source I made the string of my original file C:\FileLoc\File.accdb
No quotes around it
Front I made the beginning of the Destination C:\Access\Filename
End I made the end of the Destination 45.xls
Naturally, all of these are just examples I'm typing here as the actual values don't matter.
Then I put a File System Task on the page and clicked it to select it. I created a variable with the scope of File System Task called Conc
I made it a string like the other 3 and then went to the properties panel for that variable. I changed EvaluateAsExpression to True and then built the expression there! Right under the property EvaluateAsExpression there's a field called Expressions and you can set the expression there instead of in the File System Task.
I went back into the File System Task, set it up as a Rename File
IsSourcePathVariable True
SourceVariable User::Source
IsDestinationPathVariable::True
DestinationVariable User::Conc
I ran it, it worked like a charm. I also tried it as Copy File and that worked just fine too. that's actually the outcome I want as Rename is good for Moving the file and I want the original left where it was as it is a template. I believe you'll want to use Rename.
I hope this works for you!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 4, 2010 at 2:31 pm
Alvin Ramard (10/4/2010)
Stefan Krzywicki (10/4/2010)
Alvin Ramard (9/8/2010)
It looks like it's trying to find a variable with the name "\\Server\MyPath\Inbound\MyFile_20100901.zip"Did you happen to use the expression for the value of the variable where you should have used the variable's name?
Alvin,
I'm running into the same problem. My expression is
@[User::SourceName] + @[User::FiscalWeek] + ".acccbd"
It evaluates just fine, but then SSIS seems to think the variable name is the string that results, not the variable @[User::CombinedName] that contains the expression.
What property or variable are you assigning that expresion to?
Exactly! I figured it out (I think. I have mine working, I hope it works for Brandie too.) and it was because I was creating the expression in the File System Task instead of in the containing variable. Checking out the other properties showed me I was creating the expression in the wrong place.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 4, 2010 at 2:53 pm
Glad you got that fix.
You can use the expression in the FileSystem Task, but if you set IsSourceVariable = TRUE and then assign the result of the expression to the Source, then the Task will take the result of the expression as the variable name, not the variable value. IsSourceVariable must be set to FALSE in this case.
As I believe I said earlier, I suspect you were assinging the result of the expression to a property that was expecting a variable name, not it's value.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 4, 2010 at 3:39 pm
Alvin Ramard (10/4/2010)
Glad you got that fix.You can use the expression in the FileSystem Task, but if you set IsSourceVariable = TRUE and then assign the result of the expression to the Source, then the Task will take the result of the expression as the variable name, not the variable value. IsSourceVariable must be set to FALSE in this case.
As I believe I said earlier, I suspect you were assinging the result of the expression to a property that was expecting a variable name, not it's value.
The confusing thing is that it looks like you're passing it the variable, but SSIS interprets it as the result and doesn't give you much that's useful in the error that results.
IsSourceVariable is still set to TRUE in this case. Or IsDestinationVariable is set to TRUE, whichever you're creating as an expression. By the time the expression is created, by creating it in the variable instead of in the task, File Task Manager sees it as a variable.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 4, 2010 at 11:03 pm
My best bet would be that since all these variiables are in the FEL the best would be to assign the value of ZipFileName variable to a fourth variable - a temporary variable and using the temporary variable carry the process of archiving the existing file(s)
Also, have you implemented any checkpoints in your package...
Please refer the following link[/url]
Raunak J
October 5, 2010 at 4:47 am
Stefan Krzywicki (10/4/2010)
Then I put a File System Task on the page and clicked it to select it. I created a variable with the scope of File System Task called Conc
I made it a string like the other 3 and then went to the properties panel for that variable. I changed EvaluateAsExpression to True and then built the expression there! Right under the property EvaluateAsExpression there's a field called Expressions and you can set the expression there instead of in the File System Task.
I went back into the File System Task, set it up as a Rename File
IsSourcePathVariable True
SourceVariable User::Source
IsDestinationPathVariable::True
DestinationVariable User::Conc
I ran it, it worked like a charm.
I hope this works for you!
Yeah, but that's not the way I wanted to use the File System task. I was trying to avoid the creation of unnecessary variables and just re-use the ones I'd already had. It didn't work.
As far as where to report bugs, it's somewhere on Tech Net. I believe if you google for it, you can find the link.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply