BCC
I can think of a couple of ways off the top of my head, but there are probably others.
1. Using variables and expressions.
2. Using a script task(!)
The choice depends to some extent on whether you are just producing one file and therefore just need to append or prepend the date to a single filename, or, you need to loop through multiple files and so have a different filename each time, in which case a Script Task combined with a For Each loop would probably be the way to go.
For a single file, create two variables - one to store the filename and one to store the current date which you can get from executing a simple Execute SQL task, returning getdate() and doing a CAST or CONVERT to get the result in the format you want, e.g. yyyy-mm-dd.
You can then use an expression on your filename variable to append or prepend the contents of the date variable.
To illustrate the idea, the first attached screenshot shows a variable in one of my packages (in fact I have this variable in all my packages) whose value is hardcoded, i.e. no set using an expression.
The second screenshot shows a second variable whose value is set dynamically using an expression, based on the value of the first variable.
If you go down the Script Task route and need to process multiple files then something like this script will give you a list of files which you can then pass to a For Each loop. Within the For Each loop you could append or prepend the date to the filename of each individual file:
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections
Imports System.IO
Public Class ScriptMain
Private listForEnumerator As ArrayList
' This script builds an array of *.xls files that is passed to an Object type
' variable for later use in a ForEach loop.
Public Sub Main()
Dim varSourceDirectory As String
Dim varLocalFileList() As String
Dim varLocalFile As String 'to store full path and name of each file
Dim varFirstLevelSubFolder As String 'to store name of each sub-folder
Dim varProviderCount As Int16
Dim varArrPath As Array
Dim varProviderName As String
Dim vd As VariableDispenser = Dts.VariableDispenser
Dim vars As Variables
vd.LockForRead("vSourceDirectory") 'passed in from Package
vd.LockForRead("vExecuteFor") 'contains value of /SET option in job step used to execute package
'for a single Provider
vd.LockForWrite("vFileList") 'will hold the array contents
vd.LockForWrite("Cnt_ProviderCount")
vd.GetVariables(vars)
Try
listForEnumerator = New ArrayList
varSourceDirectory = vars("vSourceDirectory").Value.ToString
'If sub folders exist one level below the SourceDirectory, look in each
'sub folder for .xls files and add them to the array.
If Directory.GetDirectories(varSourceDirectory).Length > 0 Then
varProviderCount = 0
For Each varFirstLevelSubFolder In Directory.GetDirectories(varSourceDirectory)
varLocalFileList = Directory.GetFiles(varFirstLevelSubFolder, "*.xls")
varArrPath = Split(varFirstLevelSubFolder, "\")
varProviderName = varArrPath(UBound(varArrPath))
'MsgBox("Provider: " & varProviderName, MsgBoxStyle.Information)
For Each varLocalFile In varLocalFileList
listForEnumerator.Add(varLocalFile)
'MsgBox("File name: " & System.IO.Path.GetFileName(varLocalFile), MsgBoxStyle.Information)
Next
varProviderCount += 1
If varProviderName = vars("vExecuteFor").Value.ToString Then
Exit For
End If
Next
End If
'
'MsgBox("Number of Providers: " & varProviderCount.ToString, MsgBoxStyle.Information)
vars("vFileList").Value = listForEnumerator 'Pass the contents of the array to an object variable for use later in the package
vars("Cnt_ProviderCount").Value = varProviderCount
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, "", ex.Message & ControlChars.CrLf & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
vd = Nothing
End Sub
End Class
There are many good examples of using Script Tasks by Jaime Thomson at http://blogs.conchango.com
HTH
Lempster