Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


to rename a file by attaching datestamp at the end of filename using SSIS File task


to rename a file by attaching datestamp at the end of filename using SSIS File task

Author
Message
Alicia Rose
Alicia Rose
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
Hi SSIS Gurus,

I have a dataflow task that creates a text file and posts it in a FTP share.
In need to rename the file attaching datestamp(current date of package execution) at the end of filename.
For ex: My dataflowtask creates a file called 'Samplename.txt'

I need to rename it to : 'Samplename20080225.txt.

I think it can be done using the File task...I see an option to rename the file but not sure of how to configure the task to attach current datestamp.

Suggestions and Help would be highly appreciated.

Thanks,

Alicia Rose
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
You could also acomplish this within a script task. Let's assume you have the following variables:

FileDirectory, string, C:\TEMP
FileName, string, SomeFileName.csv


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain
Public Sub Main()
Dim strDate As DateTime = DateTime.Now
Dim strNewFileName As String
strNewFileName = "SampleName" & DateTime.Now.ToString("yyyyMMdd") & ".txt"
'Another Example to use yesterday's date
'strNewFileName = "SampleName" & DateTime.Now.Subtract(New TimeSpan(1, 0, 0, 0)).ToString("yyyyMMdd") & ".txt"
'
Try
File.Move(Dts.Variables("FileDirectory").Value.ToString & "\" & Dts.Variables("FileName").Value.ToString, Dts.Variables("FileDirectory").Value.ToString & "\" & strNewFileName)
Dts.Events.FireInformation(0, "", "File Renamed Succesfully", "", 0, True)
Catch ex As Exception
Dts.Events.FireError(0, "", "Source File Does Not Exist", "", 0)
End Try

'For Debugging Purposes
'System.Windows.Forms.MessageBox.Show(Dts.Variables("FileDirectory").Value.ToString & "\" & Dts.Variables("FileName").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub

End Class



Tommy

Follow @sqlscribe
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
The easier and preferred method is to simply create a variable for the path and use get date functions. Click the connection manager for the output text file --> View the properites --> expand "Expressions" and modifiy the connection string as follows:

Note: @[User::SharePath] is a variable that holds the path.


@[User::SharePath] +
RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".txt"





My blog: http://jahaines.blogspot.com
Alicia Rose
Alicia Rose
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
Thank You So much.... Adam, for providing a simple solution .

Alicia Rose
Alicia Rose
Alicia Rose
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
Thanks Tommy!!!

Alicia Rose
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
No Problem BigGrin

Thanks for the feedback.



My blog: http://jahaines.blogspot.com
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
NP Smile

Tommy

Follow @sqlscribe
Ulrich Bauhofer
Ulrich Bauhofer
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 347
Click on your Flatfile-Connection. In the Property Window you can see 'Expressions'. Click on the ellipsis (...) and select the property 'Connection string'. There you can create your filename with the something like "c:\\yourfile" + Replace(Replace((DT_WSTR,15)(DT_DBDATE)GETDATE() + (DT_WSTR, 15)(DT_DBTIME)GETDATE(),":",""),"-","") + ".csv"
Greg Caporale
Greg Caporale
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 62
Hello, I am trying to accomplish the exact same thing. I have created a variable called Path and on the connection for my flat file I have changed the ConnectionString Expression to be:

@[User:Tongueath] + "Holdings_" + (DT_STR,4,1252) DatePart("yyyy",getdate()) + Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".csv"

However I am not getting the path, all I get is Holdings_20080227.csv. This file is on a mapped drive on my server and so it should be saving to that shared drive.

Is it possible to just hard code the path?

Thanks
Greg
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
Try -


@[User:Tongueath] + "\\" + "Holdings_" + (DT_STR,4,1252) DatePart("yyyy",getdate()) + Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".csv"



Tommy

Follow @sqlscribe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search