Hi,
Can someone help me with the following

I get a file each week with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in Customers
table .
How do I tell DTS that the file name changes every week. I am using Text
file connection & I have specified the file name.
For example
C:\Work\customersA1.txt

Earlier I had posted it few days ago & I was refered to a website which had
following code.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Dim oConn, sFilename
sFilename = "customer" & Right(Year(Now()), 2)
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("FilePath").Value & sFilename & ".txt"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename
set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function


I am not good in activex programming. I am just a beginner in activex. Can
someone show me with syntax(activex) what to write in order to determine that
filename has changed.


A complete syntax will be lot of help for me. Please use
C:\Work\filename.txt as path so I can understand it well




Thanks

Steve

OK.

To be able to use any code to automagically rename the file connection to
the correct one you have to be able to perform the logic to derive that
name. Can you? How do you _KNOW_ what the file will be called.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Steve" <Steve (AT) discussions (DOT) microsoft.com> wrote

See the loop example at http://www.sqldts.com/default.aspx?246

We use it to download files from FTP site, loop through all files downloaded
and process them into our tables. Works like a champ and we don't care the
file name because we get is from each file right before we process. We then
delete the file if success or move to an error directory if failure. We
have modified the example they provide to our needs as can you.

Scott

"Steve" <Steve (AT) discussions (DOT) microsoft.com> wrote