Text File Connection file name

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.