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 12345»»»

to rename a file by attaching datestamp at the end of filename using SSIS File task Expand / Collapse
Author
Message
Posted Monday, February 25, 2008 3:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 28, 2011 12:49 AM
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
Post #459991
Posted Tuesday, February 26, 2008 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:25 AM
Points: 1,157, Visits: 3,269
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

Post #460231
Posted Tuesday, February 26, 2008 7:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 2,278, Visits: 3,065
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
Post #460242
Posted Tuesday, February 26, 2008 11:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 28, 2011 12:49 AM
Points: 32, Visits: 172
Thank You So much.... Adam, for providing a simple solution .

Alicia Rose
Post #460405
Posted Tuesday, February 26, 2008 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 28, 2011 12:49 AM
Points: 32, Visits: 172
Thanks Tommy!!!

Alicia Rose
Post #460406
Posted Tuesday, February 26, 2008 11:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 2,278, Visits: 3,065
No Problem :D

Thanks for the feedback.




My blog: http://jahaines.blogspot.com
Post #460411
Posted Tuesday, February 26, 2008 11:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:25 AM
Points: 1,157, Visits: 3,269
NP :)

Tommy

Post #460421
Posted Wednesday, February 27, 2008 2:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:50 AM
Points: 58, Visits: 332
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"
Post #460722
Posted Wednesday, February 27, 2008 10:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 23, 2012 9:56 AM
Points: 32, Visits: 60
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::Path] + "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

Post #461100
Posted Wednesday, February 27, 2008 11:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:25 AM
Points: 1,157, Visits: 3,269
Try -

@[User::Path] + "\\" + "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

Post #461140
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse