Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rename File in SSIS Expand / Collapse
Author
Message
Posted Thursday, August 28, 2008 1:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:38 PM
Points: 1,155, Visits: 548
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
Posted Thursday, August 28, 2008 2:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 5, 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
Posted Thursday, August 28, 2008 4:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, September 3, 2008 3:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, September 3, 2008 3:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, September 3, 2008 5:12 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, September 3, 2008 5:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, September 3, 2008 7:29 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Thursday, September 4, 2008 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, September 4, 2008 10:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 12»»

Permissions Expand / Collapse