Export Data by DTS, file cannot be created

I used DTS to create a package to export data from a table in SQLServer2000 to a .csv file.

I set up two connection and define a file dsn for destination file.

My problem is:
If I create a THE.csv file and put in the folder, the package can copy the data over. But if the file is not there, I got an error.

My question is: Isn't the database suppose to create a .csv file when I export any data?

It's urgent. Please help! Thanks,

LiliI just ran a little test on my machine, using PUBS. I created a connection to my local machine using Microsoft OLE DB Provider for SQL Server and then created a destination using Text File (Destination). I set the destination to 'C:\Temp\pubs.csv'. The file did not exist and when I ran it, the file was created. I deleted the file and reran and once again the file was created.

My guess is that you did not use Text File (Destination).|||THANKS!!! You saved my life ;)

Yeah, you're right. I didn't choose text destination. I used file dsn for import so I took it for granted that I need to use file dsn for export. Silly me //blush.

But one more thing, the file I exported now doesn't have a top line with all column names. I remember there's a checkbox I can check to get it. But I cannot find it. Would you tell me where it is?

Many thanks,

Lili|||Right click the destination file and pick Properties. On this screen below where you enter the destination file name you should see a button Properties... This opens a new screen with a checkbox labelled First row has column names|||I knew there is a checkbox! :) Got it. Thanks, guru.

I'm a developer instead of DBA so it's new experience for me to do all database stuff. It's interesting though.

Now I have one more question about DTS. I created a package to import data. Then I called it from command line(dtsrun with parameters) and it works fine. My question is, is it possible to pass in a filename(data file) so that the package can import from dynamic filename instead of a static one?

Thank you in advance,

Lili|||Sure you can.

You'll need to create a table in the database that holds the name of the file. For example create a table called DTSRunTime, with one column called Parameter and another one called Value. Then insert into the table one record "ImportFile","C:\Temp\abc.txt", just to have a record in it. Then in a batch file prior to run the DTS package run ISQL that updates the record.
Example:isql -Usa -P -Sxxx -ddb -n -Q"Update DTSRunTime set Value='C:\temp\new.txt' WHERE Parameter='ImportFile'" Then you need to create a ActvieX Script Task that will read the database table and set the Import File.
Example:
'**************************'
Visual Basic ActiveX Script
'**************************'
Function Main()
Dim cnADODB
Dim rs
Dim strFileName
Dim oPKG
Dim cn

Set cnADODB = CreateObject("ADODB.Connection")
cnADODB.Open "Driver={SQL Server};Server=xxx;Database=db"

Set rs = cnADODB.Execute("SELECT value FROM DTSRunTime WHERE Parameter='ImportFile'")
strFileName = rs("value")
rs.Close
cnADODB.Close
Set cnADODB = Nothing

Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)")
cn.DataSource = strFileName

Main = DTSTaskExecResult_Success
End FunctionIf the import file name is something that can be generated then you don't need to go the table route. Let's say the import name if "c:\temp\yyyymm.dat". Then create a ActiveX Script like
'**************************'
Visual Basic ActiveX Script
'**************************'
Function Main()
Dim oPKG
Dim cn
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)")
cn.DataSource = "C:\File_" & Year(Now()) & Month(Now()) & ".dat"

DTSGlobalVariables("InputFile").Value = cn.DataSource

Main = DTSTaskExecResult_Success
End Function