February 25, 2008 at 3:05 pm
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
February 26, 2008 at 7:29 am
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 @sqlscribeFebruary 26, 2008 at 7:45 am
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"
February 26, 2008 at 11:02 am
Thank You So much.... Adam, for providing a simple solution .
Alicia Rose
February 26, 2008 at 11:04 am
Thanks Tommy!!!
Alicia Rose
February 26, 2008 at 11:08 am
February 26, 2008 at 11:22 am
NP 🙂
Tommy
Follow @sqlscribeFebruary 27, 2008 at 2:01 am
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"
February 27, 2008 at 10:30 am
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
February 27, 2008 at 11:21 am
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
Follow @sqlscribeFebruary 27, 2008 at 11:36 am
Thanks Tommy, but I must be missing something. When I do as you suggested I get "\Holdings_20080227.csv" in the evaluated expression.
Its almost like my variable "Path" isn't being populated.
Greg
February 27, 2008 at 12:06 pm
NP, Good luck 🙂
Tommy
Follow @sqlscribeFebruary 27, 2008 at 1:48 pm
Your variable should look like this: \\myserver\e$\. You should not have to do any fancy manipulation here.
Make sure your variable is a string and in the scope of your current dtsx package. Additionally,
you can select your variable from the variable listing in the expression creator.
February 27, 2008 at 2:04 pm
Adam,
Thanks for replying. I created the variable "Path" and it is available in the expression creator, but it just doesn't pull in the actual path. In the expression editor it is [User :: Path] (without the spaces) but do I need to define that somewhere else or is that some sort of keyword like the old %windir%?
February 27, 2008 at 2:16 pm
In the expression editor it is [User :: Path]
This means you are using a user variable named Path. Select the variable named Path
from the list and drag it down into the expression editor.
You variable should be written like this: @[User :: Path]. Note the @ sign before the variable.
Once the variable is in the expression editior, click the evalute button to verify the variable.
The results should be the file path you specified. If not you you have your variable configured
incorrectly.
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply