Text File Connection file name

Sunday Nov 28th 1999 by Darren Green
Share:

Changing the filename of a Text File connection using ActiveX script. This also demonstrates using ADO and DTS Global Variables in Active Script Tasks.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved