Rename File in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Rename File in SSIS

13 posts, Page 1 of 2

1 2 »»

Rename File in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

Erin.

Posted Thursday, August 28, 2008 1:24 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 31, 2013 4:58 PM
Points: 1,151, Visits: 533

I need to rename a file in SSIS. Part of the file rename should include the current date.

I am trying to use the File System Task, but I don't know how I can rename the file. The file name should look like the following: TimeCommUpdatesyyyymmdd.xls

I'm thinking I may have to use expressions, but I can't find any good documentation on the subject.

Any help would be appreciated.

Thanks,
Erin

Post #560772

VG-619426

Posted Thursday, August 28, 2008 2:06 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, August 05, 2010 11:54 AM
Points: 222, Visits: 676

Here is the solutin provided by one of sql server central member

http://www.sqlservercentral.com/Forums/Topic459991-148-1.aspx?Highlight=rename

VG

Post #560801

SSIS\SSRS\SSAS

Posted Thursday, August 28, 2008 4:34 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

ERIN i would recommed you to use variables and expressions to do it.
First create 6 variables--
1.filename - abc (what ever u want, we will pull the value from ForeachLoop

2.SourcePath - give the path name as D:\folder\folder\

3.SourceFullfilepath - put this in expression(f4 + evalate as expression and then build the expression with source path + filename+ extension

4.ArchivePath- give the path D:\folder\folder\Archive\

5.ArchiveFullfilePath- put this one in expression as

@[User::ArchivePath] +  @[User::FileName] + (DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2)+"_" + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GetDate()), 2) + @[User::FileExtension]

6.FileExtension- extension of ur choice

Then drag Foreachloop container , configure as for eah file found return only file name

Then map the variable file name.
Bring the File system task and for destination true as varaiable give the fullarchivepath
and source as varaibale fullsourcevariable

Let me know if u need more hints
thanks

Post #560892

sonia-834492

Posted Wednesday, September 03, 2008 3:02 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:49 AM
Points: 33, Visits: 176

i am trying to do the same but here i have to rename multiple files in a folder with the current date. when i execute the package i get the following error:

[File System Task] Error: An error occurred with the following error message: "The given path's format is not supported.".

Can somebody suggest me when I am going wrong. I verified all the expressions used for the variables in the file path names and they look good.

Post #563411

sonia-834492

Posted Wednesday, September 03, 2008 3:24 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:49 AM
Points: 33, Visits: 176

for the above, i executed the file sytem task for renaming for a single file without using the foreach loop container and it works fine but when i put the task into the container then it is giving the above mentioned error. please suggest me where i might be going wrong with the foreach loop container.

Post #563422

SSIS\SSRS\SSAS

Posted Wednesday, September 03, 2008 5:12 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

sonia its clearly says that the path format is not supported. I am doing all my renaming copy and archive all the time like this , it works. may be you have something wrong in the return. only return file name and use a extension in the variable.

Post #563486

sonia-834492

Posted Wednesday, September 03, 2008 5:48 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:49 AM
Points: 33, Visits: 176

hi gyanendra,

i have specified all the values in my tasks. can you go over them and let me know where the error might be. i checked the path names for all the expressions and they look correct to me.

the files- file1.txt,file2.txt and file3.txt are in sample folder( source) and need to renamed and moved to archive folder(dest)

These are the variables that i am using for this job:

foreach loop container:
enumerator: for each file enumerator
folder = d:\sample
files = *.txt
variables mapping = user::myfilevalue

file system task:
isdestinationpathvariable = true
destinationvariable = User::fulldestpathfilename
operation = rename
issourcepathvariable = true
sourcevariable = User::fullsourcepathfilename

variables:

myfilevalue = file1.txt
dest path = d:\archive
source path = d:\sample
fullsourcefilepathname = @[user:: sourcepath] + @[user:: myfilevalue]
fulldestfilepathname = @[user:: destpath] + substring( @[User::myfilevalue],1,findstring( @ [User::myfilevalue],".",1)-1) + "-" + (DT_WSTR,2)month( @[System::StartTime]) + (DT_WSTR,2) day( @[System::StartTime]) + (DT_WSTR,4)year( @[System::StartTime]) + substring( @[User::myfilevalue], findstring( @[User::myfilevalue], ".",1) , LEN( @[User::myfilevalue] ))

the above is supposed to give a result of file1932008.txt,file2932008.txt and file3932008.txt

i know there is some very simple error involved and this is supposed to be an easy one but somehow cant resolve it.

Post #563495

SSIS\SSRS\SSAS

Posted Wednesday, September 03, 2008 7:29 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

First of all , I don't know what you are returning from foreachloop conatiner-- fullpathfile, or name and extention, or just name. If i were you i would go just return teh file name in the for each loop container( you know what i am takling rt) there is a option where what you want to specify what to return back and then map this to a variable. and then create a variable called "fileExtention" and assign the value that corresponds to your extension choice like ".txt"

then this way you avoid using substring function in the archivedestination. Then i am not sure your other function to get date works or not. ( i am hoping it is ) Then also make sure ur 2 variables for arhive destination and source are using expressions. highlight the variable and then press f4 , it will open the expreeion builder for variables and set evalute as expression as true and then build your expression over there with adding file extension at the end. in the expression for source
bring your source variable + file name( name return from foreach loop) + extension and for archive
build expression as archivefolder variable + filename + all the functions that will return date portion + file extension

this should work out.

Post #563516

sonia-834492

Posted Thursday, September 04, 2008 10:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:49 AM
Points: 33, Visits: 176

i created a fresh one and did it in the way you had mentioned earlier in one of your posts by creating 6 variables, but still i get the same error. i need to make sure of one thing. in the foreach loop container, for the variable mapping section -- here do we just give some arbitary name or the exact file name of one of the files in the folder. i tired both ways but still getting the same error.

the file system task works for a single file. so i think there must be something wrong with the foreach loop container.

i appreciate your help in this matter.

Post #563954

sonia-834492

Posted Thursday, September 04, 2008 10:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:49 AM
Points: 33, Visits: 176

It finally worked for me. I figured out the reason for my error. in the foreach loop container for the retrive file name section, I had checked fully qualified option. when I changed it to name and extension, it worked. I am able to rename and move my files to archive folder. :):)

Post #563970

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions