Create SSIS File Name Dynamically

After your (Data Flow Task?) dumps the data into your flat file, create and connect a Script Task directly after that. Below is an example of code that I used after I dump data into a CSV file (VB 2008), SFTP the file to a vendor, and then rename my file with a date/time stamp and move the file to a processed folder.

Hopefully, this will give you a good idea of how to move forward with your task .. copy and paste this code into your Script Task (make sure to select VB code within the Script Task configuration) and then modify as needed.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

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

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum

    'Move destination file to processed location
    'Delete destination file from destination location

    Public Sub Main()

        Dim CurYear As String = System.DateTime.Now.ToString("yyyy")
        Dim CurMonth As String = System.DateTime.Now.ToString("MM")
        Dim CurDay As String = System.DateTime.Now.ToString("dd")
        Dim CurHour As String = System.DateTime.Now.ToString("hh")
        Dim CurMin As String = System.DateTime.Now.ToString("mm")
        Dim CurSec As String = System.DateTime.Now.ToString("ss")
        Dim CurYearMonthDate As String = CurYear + CurMonth + CurDay + CurHour + CurMin + CurSec

        Dim DestFile As String = "N:\SSIS\SourceFile\"
        Dim ProcessedLocation As String = "N:\SSIS\SourceFile\Processed"

        'Move file
        For Each fi As FileInfo In New IO.DirectoryInfo(DestFile).GetFiles("MyFile.csv")
            fi.MoveTo(ProcessedLocation & "\" & CurYearMonthDate & "_" & fi.Name)
        Next

        'Delete destionation file if it exists
        If System.IO.File.Exists(DestFile) Then
            System.IO.File.Delete(DestFile)
        End If

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

Select allOpen in new window

Hi, thanks for the quick response from all.  I used the script and made the changes below:
    Public Sub Main()
        'Dim CurYear As String = System.DateTime.Now.ToString("yyyy")
        'Dim CurMonth As String = System.DateTime.Now.ToString("MM")
        'Dim CurDay As String = System.DateTime.Now.ToString("dd")
        'Dim CurHour As String = System.DateTime.Now.ToString("hh")
        'Dim CurMin As String = System.DateTime.Now.ToString("mm")
        'Dim CurSec As String = System.DateTime.Now.ToString("ss")
        'Dim CurMonthDateYear As String = CurMonth + CurDay + CurYear + CurHour + CurMin + CurSec
        'Dim DestFile As String = "C:\Users\bobbid\CBB_Data_Exchange\"
        'Dim ProcessedLocation As String = "C:\Users\bobbid\CBB_Data_Exchange\Processed"

        '''Move file
        'For Each fi As FileInfo In New IO.DirectoryInfo(DestFile).GetFiles("CBB_Practice_File.txt")
           fi.MoveTo(ProcessedLocation & "\" & fi.Name & "_" & CurMonthDateYear)
         Next

        '      ''Delete destionation file if it exists
        If System.IO.File.Exists(DestFile) Then System.IO.File.Delete(DestFile)
        End If

        Dts.TaskResult = ScriptResults.Success
    End Sub

The file appears like this: CBB_Practice_File.txt_07252013075552  ...  I need the date before the .txt   And need to separate the hh mm ss with colon (:) between.

Also, I tried to use the expression but what do I put in the properties section?

Many Thanks again,
BobbiDee

Can someone please answer my questions:

how can I get MMddYYYY hh:mm:ss in the script above to work.  i have tried
'Dim CurMonthDateYear As String = CurMonth + CurDay + CurYear + ":" + CurHour + CurMin + CurSec


or

how do i use the expression - what do i choose as the property in the script.

Thanks,
Bobbidee

OK.  I figured it out on how to use the expression.  I was originally using the expression on the script which did not give an option for connection string - used the expression on the file connection.  

The script worked fine except that I could not separate the hours miniutes seconds with a colon e.g. hh:mm:ss.

Thank you for getting back - I will try this and hope that they won't mind not having the colon in the hour / mins.

I will get back to you and let you know if it works!

Bobbi

Hi, this did not work.  The file was processed without the date and txt. Not recognized as a txt file.

Bobbi