DTS - Dynamic name of files

[claydson]

I have a package that export the data from table to text file, and I need to create this text file with different names, like ("file001","file002",...).
I'm thinking in to use a gloval variable in the file name property, like this: C:\Claydson\ETL\DTS\[variable], but it isn't working.
Does anybody know how can I do this?

[MAK]

1. Create the Following global variables in the DTS package

TargetFileVar
TargetFilePath

2. Create the Following connections
SQLBox for SQL Connection
TargetFile for output TExtFile connection

3. Add transformation between to SQLBox and TargetFile

4. Create the below activex script

'************************
' Visual Basic ActiveX Script
'************************
Function Main()

mydate =now()
sFilename = DTSGlobalVariables("TargetFilePath").Value & DTSGlobalVariables("TargetFileVar").Value & ".txt"
Set oConn = DTSGlobalVariables.Parent.Connections("TargetFile" )
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

5. Make the Activex script as the first step in the package

6. Save the package as structured storage file

6. Run the package using the below syntax

dtsrun -E -F"ExportVar" -A"TargetFilePath"="C:\claydson\ETL\
DTS\" -A"TargetFileVar"="File00"

[MAK]

please find attached DTS package

[MAK]

http://www.databasejournal.com/featu...le.php/2225481

[claydson]

Originally posted by MAK
http://www.databasejournal.com/featu...le.php/2225481

Thanks MAK very much. It worked very well! I have another question: I need to process text files as source data, and I don't know its names, I know only the path where they are located. How can I "discover" the name of the files in a path and use it into my package?

[MAK]

http://www.databasejournal.com/featu...le.php/3325731

[Avadhoot]

I am scheduling DTS package to generate output in text file. I am using stored procedure for generating data. In that stored procedure I am creating temporary table and deleting it after its usage. It works fine in SQL Analyzer however fails in scheduling as DTS. Can anyone tell why it happens? thanks...