Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 28, 1999

Text File Connection file name

By Darren Green

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM