Hello,

I have a dts package that creates a csv file in a multi-user environment.
How can I dynamically specify the output location so that it goes into each
users "My Documents" folder?

Thanks,

Vic

Here is the script that I'm trying to get to work. It operates ok if I do
not change the Output file name & location of the destination so something
is wrong with how I am specifying the destination file name. Can someone
tell me what is wrong with the following script? The input GlobalVariable is
working ok but the Output variable is not be assigned to the destination
file name.

Thank You

Vic


Function Main()
Dim oPkg, oDataPump, sSQLStatement, sFileName

' Build new SQL Statement
sSQLStatement = "SELECT * FROM dbo.vuCoverLetter WHERE JobID = '" & _
DTSGlobalVariables("JobID").Value & "'"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Following statements assign the output file name and location

sFilename = DTSGlobalVariables("OUTPUT").Value
oDataPump.DestinationObjectName = sFileName

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

Have you ever tried the FileSystemObject yet?
That is a very common way to move data around

Ed

"Vic Spainhower" wrote:

In message <#knpZHy3EHA.2600 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Vic Spainhower
<vicNoSpam (AT) perfected (DOT) com> writes
The file name should be set on the destination connection in this case,
the DataSource property.

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren,

Thank you that did the trick ...


Vic


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Ed,

Yes, I've used FSO and maybe it would be more appropriate, I don't know. But
I decided to use dts just so that I could learn more about it.

Vic


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