Solved

File System Task to rename file giving failed to lock variable error

Posted on 2008-10-29
10
3,948 Views
Last Modified: 2013-11-10
I have a File System Task in place to rename a file
File Exist in the following folder in this format [This is the Source]
This file would be available daily,with current timestamp
C:\A_20081029.txt
File at source to be renamed to [At Destination,which is the same location as Source]
C:\A.txt

Here is what has been done so far in File System Task
Is Destination Path Var : True
DestinationVar : User :: DestinationFileName
OverwriteDestination : True

Is Source Path Var : True
Source Var :: SourceFileName

In Expression Tab
Create new Expression
Source : @[User::SourceFileName]+ (DT_STR,4,1253)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1253)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1253)DAY(GETDATE()),2) +".txt"
Evaluate Return : C:\A_20081029.txt

SSIS Tab > Variable Created as

SourceFileName  /  String  /  Value : C:\A_
DestinationFileName / String / Value : C:\A.txt

When I execute,I get an error mssg as below

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at File System Task [File System Task]: An error occurred with the following error message: "Failed to lock variable "C:\A_20081029.txt" 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.".
".

Error at File System Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

0
Comment
Question by:analliah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22831863
Since you are using an expression to create the source, change the Is Source Path Variable to False.
0
 
LVL 5

Author Comment

by:analliah
ID: 22831914
Changed and this is what I get

TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at   File System Task: The connection "C:\A_20081029.txt" is not found. This error is thrown by Connections collection when the specific connection element is not found.
 

------------------------------
BUTTONS:
OK
------------------------------
 
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22831953
Since this is your Source: C:\A_20081029.txt. Does that file exist? If not, is this your destination? Make sure you have the two correct.
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 5

Author Comment

by:analliah
ID: 22832005
The C:\A_20081029.txt is my Source and it exist in the specified folder.
I would like to rename & overwrite C:\A_20081029.txt to C:\A.txt in the Source folder which is also the Destination folder.
How is this possible ?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22832060
Also note, the DestinationConnection and SourceConnection are "connection" objects in the Connection Managers window. Make sure you have a connection for each file.
One thing I suggest is creating defining the DestinationConnection and SourceConnection properties (ConnectionString) using an expression.
I am going to attach an example in the next post. Here is a pic.

rename-file-task.bmp
0
 
LVL 5

Author Comment

by:analliah
ID: 22832656
I managed to solve it,appreantly I had not set the Expression to the variable and set the EvaluateAsExpression to True.
I have created 3 var now as below.
SourceFileName  /  String  /  [@User::SourceLocation] +(DT_STR,4,1253)YEAR(GETDATE()) +
RIGHT("0" + (DT_STR,2,1253)MONTH(GETDATE()),2) + RIGHT("0" +
(DT_STR,2,1253)DAY(GETDATE()),2) + ".txt"

Sourcelocation  / String /  Value : C:\A_

DestinationFileName / String / Value : C:\A.txt
In the File System task .I have set the following
Destination is : DestinationFileName
Source is : SourceFileName
 
Thank You for your comments !
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22832813
Great! I am going to post my instructions for future reference on this subject.
 

Rename-a-File-using-SSIS.doc
0
 
LVL 5

Author Comment

by:analliah
ID: 22833732
Great documentation.
I'm gonna be keeping this as your method is slightly different them mine.
Thanks For The Help
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 26405861
Question PAQ'd, 250 points refunded, and stored in the solution database.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert varchar UTC to human datetime 1 38
SQL Query Syntax error after > 11 39
T-SQL: Stored Procedure Syntax 3 29
Specify timing interval fro change data 2 53
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question