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

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").Va lue)
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

...

"mayer4 (AT) gmail (DOT) 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.

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




"mayer4 (AT) gmail (DOT) com" wrote:

Thanks for the tip. I will look into that.

Thanks again.

Laura