Change filename for flat file connection manager at runtime

I need to add the current date to the end of the filename of a flat file at runtime.

This was previously done with ActiveX script in SQL Server 2000.

oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName")
oConn.DataSource = sNewFileName
oConn = Nothing

I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005.
Thanks in advance!

Regards,
Sara

Hi Rishi,

I did something to fix it then, but I would't solve it the same way today. The following example gets data from a file with a date in it. The date is always the current date so the Connection string needs to be updated to be able to read from the file.

I hope this gives you an idea of how to solve your problem!

Regards,
Sara
-----------------------------------------------------------------------------------------

Script Task
Connection Manager Name: L:\VPKBA\Laddning\EDBKjerne\konto_YYYYMMDD.txt
File Name: L:\VPKBA\Laddning\EDBKjerne\konto.txt

Imports System

Imports System.IO

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim theDate As String

Dim i As Integer

strPath = "L:\vpkba\laddning\EDBkjerne\"

theDate = Now.Year & IIf(CInt(Now.Month) < 10, "0" & Now.Month, Now.Month).ToString & IIf(CInt(Now.Day) < 10, "0" & Now.Day, Now.Day).ToString

For i = 0 To Dts.Connections.Count - 1

If Dts.Connections(i).Name.Contains("YYYYMMDD") Then

Dts.Connections.Item(i).ConnectionString = Dts.Connections.Item(i).Name.Replace("YYYYMMDD", theDate)

End If

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class