SQL Server Performance Forum – Threads Archive

Creating Text File from DTS Package

Hi. It is possible for one DTS package to create more than one comma delimited file? I have created a DTS package that generates a comma delimited file from my SQL query at the same time each day, but I would like the file to have a different filename for each day. Is this possible in one DTS package, or do I need a DTS package for each day of the week? Thank you, Sue

You can accomplish the task using global variables and this linkhttp://www.sqldts.com/default.aspx?280,3 will give you idea about using them. HTH 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.

Hi, Satya. Thank you for your response I think I am close to making my DTS Package work with global variables. I started with something simple. However, I’m stuck on a type mismatch problem. My global variable, AddrDate, is a date. My Active X Script does the following: AddrDate = CONVERT(varchar(25), GETDATE(), 106) <== This same line works fine in my existing SQL query. The type mismatch is on the varchar. I’ve tried it several different ways and get different errors. I’ve never written an Active X Script before. Do you see anything wrong with this? What I really need is to export my data to a different comma delimited file each day of the week. I don’t see a way to do this with global variables because the text file I create is part of the DTS package, not my SQL query, and the global variables examples are substituting the global var for something in the SQL query. Thanks very much. – Sue

http://www.databasejournal.com/features/mssql/article.php/2225481 this helps. 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.

Hi, Satya. This article is exactly what I am looking for. Thank you! – Sue

Hi, Satya. I created my DTS package and then set up a job to run the package. The pkg has an ActiveX Script to append the current day’s date to my file name. However, sometimes when the job runs it just creates a file (file type = file) with no date appended and other times it creates the correct .txt file with the date appended (file type = text document). The job is scheduled to run (there is no scheduling set up on the package). Do you have any idea as to why it does not always append the date to the text file? Also, when I run the job manually (as opposed to when it runs because it is scheduled to run) either from the package or the job, it creates the correct .txt file with the date appended. Thank you. – Sue

]]>