SQL Server Performance

DTS package - export to txt file - mapping

Discussion in 'SQL Server DTS-Related Questions' started by kfinsrud, Jan 5, 2004.

  1. kfinsrud New Member

    I have set up a DTS for exporting data from a db table called profile to a text file.
    But in this table (profile) I have a field called documenttype which is a number refering to id in documenttype table. I want the name out in my textfile and not the number (id). How can I fetch this from the documenttype table? Is there a way for doing this in a DTS package? Can I do this through an activeX script or through global variables?
  2. satya Moderator

    You can use query statements to fetch the results as required while using DTS wizard to transform the data, I can't remember on which screen you can do so while using wizard (will come back once I workout).

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. kfinsrud New Member

    Thx,
    it works fine for one field, but I`ve several fields in my profile db table I would like to map correctly. For example also a field called author which is just a reference to the id in the people table. From the people table I would like to fetch full_name. Any help on that would be apriciated.

    //Kjetil
  4. ChrisFretwell New Member

    Write a query in QA that returns exactly what you want for your text file, including headers. Then either create this as a stored proc or run it directly in DTS and output the results from your query to your text file.

    Chris
  5. satya Moderator

    Yes ideal solution by Chris in your case, during the DTS wizard you select query based option to input the SP name which will take care of the transaction.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. kfinsrud New Member

    Thanks for the proposed solutions!
    The only trouble I got into was that DTS didn`t set up the correct transformations
    after I inserted my sql statement into it.

    //Kjetil

    Mr. Mister
  7. satya Moderator

    Reinitialise the package with correct transformations and before hand test the SP in query analyzer to make sure no issues with the code.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page