Get the collection of files in the folder using GetFolder, then walk the
items in the collection, checking whether the first 17 characters match, e.g.
doors_IR_20060904. Your collection will only have one file, correct?
Something like what I have below:
Dim oFS, colFile, oFolder, oPkg, cFile, oConn
Set oPkg = DTSGlobalVariables.Parent
set oConn= oPkg.Connections("Text File Source")
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFS.GetFolder(DTSGlobalVariables("LogFilePath").Value)
set colFile = oFolder.Files
for each cFile in colFile
if left(cFile.Name, 17) = "doors_IR_todaysDate" and right(cFile.Name, 3) =
"txt" then
oConn.DataSource = DTSGlobalVariables("LogFilePath").Value & "\" &
cFile.Name
end if
next
Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
http://www.learningtree.com/courses/134.htm Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm email alias: charles
email domain: kangai.demon.co.uk
...
[quoted text, click to view] "mayer4@gmail.com" wrote:
> Morning,
>
> Is there a way to import a text fie when you only know part of the file
> name? What I have is a file that changes dynamically based on the date
> and time. I will only have the date. The time always changes. I know
> how to pull the date as part of a text file dynamically:
>
> '**********************************************************************
> ' Visual Basic ActiveX Script
> '************************************************************************
> ' Pkg 200
> Option Explicit
>
> Function Main()
> Dim oConn, sFilename
>
> ' Filename format - doors_IR_yyyymmdd_hhmiss.txt
> sFilename = "doors_IR_" & Year(Now())
> If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
> Month(Now()) Else sFilename = sFilename & Month(Now())
> If Day(Now()) < 10 Then sFilename = sFilename & _
> "0" & Day(Now()) Else sFilename = sFilename & Day(Now())
> sFilename = DTSGlobalVariables("LogFilePath").Value & _
> sFilename & ".txt"
>
> Set oConn = DTSGlobalVariables.Parent.Connections("Text File
> (Source)")
> oConn.DataSource = sFilename
>
> Set oConn = Nothing
>
> Main = DTSTaskExecResult_Success
> End Function
>
> What do I add to say give me anything that looks like
> doors_IR_todaysDate_*.txt?
>
> Thanks
>
You could use WMI and do wildcard matching that way. If you don't know WMI
and WQL (WMI Query Language), that might not be the quickest route for now.
However, you can use the code I sent you as part of a loop to import
multiple files. For each file in the collection, you set the datasource
property, do the import, then do the next file, etc. Someone posted some code
here a few days ago which did a loop like I am talking about, and they said
they got the code from
www.sqldts.com. The post was something to do with
importing from an Excel workbook with multiple sheets. It must be on the last
two pages of these posts. Or you can look for the example script on
sqldts.com.
Cheers,
Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
http://www.learningtree.com/courses/134.htm Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm email alias: charles
email domain: kangai.demon.co.uk
[quoted text, click to view] "mayer4@gmail.com" wrote:
> No there could be multiple files.
>
> Is there someway you can say show me anything in the folder that starts
> with doors_ir_20060904_ (where 20060904 is todays date and) ends in
> ..txt? Because the structure of the file name changes everytime it's put
> out there, for example it can say doors_IR_20060904_090829_AM.txt one
> time and doors_IR_20060904_131129_PM.txt the next. The only thing I can
> say with certainty is the first three sections.The next two are the
> time and whether it is AM or PM. The files run whenever someone gets
> the urge to send them. There is no set time.
>
Thanks for the tip. I will look into that.
Thanks again.
Laura
Don't see what you're looking for? Try a search.
