SQL Server Performance Forum – Threads Archive

DTS package – export to txt file – mapping

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?

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.

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

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

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.

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

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.

]]>