Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-29
10
Medium Priority
?
4,150 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 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