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 Mar 10, 2003

Import Files with Date-stamped Filenames or Paths - Page 2

By DatabaseJournal.com Staff

Source Code of Sourcefile1path

'************************
'  Visual Basic ActiveX Script
'************************
Function Main()
Dim oConn, sFilename
'Business day calculation
mydate=now()-1
if datepart("w",mydate) = 7 then
mydate=mydate-1
end if
if datepart("w",mydate) = 1 then
mydate=mydate-2
end if

sFilename = "\\FileServer\SharedFiles\Bankthree-" &  Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  ".txt"
Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

Note: The connection parameter is case sensitive. In this statement

'Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")'

'SourceFile1' is case sensitive.

Source Code of Sourcefile2path


'************************
'  Visual Basic ActiveX Script
'************************
Function Main()
        Dim oConn, sFilename
mydate=now()-1
	if datepart("w",mydate) = 7 then
	mydate=mydate-1
	end if
	if datepart("w",mydate) = 1 then
	mydate=mydate-2
	end if

sFilename = "\\FileServer\SharedFiles\" &  Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  "\BankFour.txt"
Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile2")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

Note: The connection parameter is case sensitive. In this statement

'Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile2")'

'SourceFile2' is case sensitive.

Now create a 'success' workflow between the ActiveX script and the source file paths.

If today's date is March 3, 2003 then, when you execute the ActiveX script SourceFile1Path, the path in the connection SourceFile1 will automatically be changed to the new path "\\FileServer\SharedFiles\Bankthree-20030228.txt." When you execute the ActiveX script SourceFile2Path the path in the connection SourceFile2 will automatically be changed to the new path "\\FileServer\SharedFiles\20030228\BankFour.txt"

Now update the transformation and execute the package.

Conclusion:

Every time when you execute the package, the ActiveX scripts formulate the previous business date and prepare the full path of the source files. This script can be used for all kinds of source files like XL, .csv, .txt, .mdb etc.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date