One problem with a source or destination Text File connection, is that your file name
can change. Editing the package every time you run it can be tedious and slow.
The scripts below demonstrate how to change the file name using an ActiveX script.
Example 1: Changing the file name to a date derived value
Dim oPKG
Dim cn
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)")
cn.DataSource = "C:\File_" & Year(Now()) & ".dat"
Assuming the year is 1999 (which it is), the script above will change the file name for the connection called Text File (Source) to C:\File_1999.dat.
Example 2: Changing the file name to a Global Variable value
Dim oPKG
Dim cn
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)")
cn.DataSource = DTSGlobalVariables("Global_Variable_Name").Value
This will change the file name for the connection called Text File (Source) to the value of the package Global Variable called Global_Variable_Name.
Example 3: Changing the file name to a value held within a SQL table
Dim cnADODB
Dim rs
Dim strFileName
Dim oPKG
Dim cn
Set cnADODB = CreateObject("ADODB.Connection")
cnADODB.Open "Driver={SQL Server};Server=(LOCAL);Database=master"
Line split for display purposes
Set rs = cnADODB.Execute("SELECT value FROM master.dbo.tbVariables
WHERE variable = 'User_Variable_Name'")
strFileName = "C:\" & rs("value")
rs.Close
cnADODB.Close
Set cnADODB = Nothing
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)")
cn.DataSource = strFileName
See User Variables for a full explanation of storing values in SQL tables.
» 
Comment and Contribute