Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    23

    Unhappy Unanswered: 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,

    Lili
    DTS -> Oracle

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I 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).
    MCDBA

  3. #3
    Join Date
    Apr 2002
    Posts
    23

    Wink

    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
    DTS -> Oracle

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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
    MCDBA

  5. #5
    Join Date
    Apr 2002
    Posts
    23
    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
    DTS -> Oracle

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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:
    Code:
    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:
    Code:
    '**************************'
    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 Function
    If 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
    Code:
    '**************************'
    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
    Last edited by achorozy; 04-04-02 at 12:01.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •