Creating text file with DTS

[Azzuri]

Hi,

I'm new to using DTS and was wondering if its possible to create a text file dynamically. I know how to connect to a text and pump data into it but the problem is the text file will be used and deleted. Everything I've read so far requires you need a data source to pump data into.

If that's not possible then is there a way through DTS to copy a file from one location to antoher.

I am using SQL Server 2000 on Windows 2000 server.

Any help is much appreciated.

 

[collierd]

If you right click on the database and export it allows you to set it up such that you export from SQL to a text file

You can either set the export to use a predefined query, i.e. create in query analyser, run to check it works then cut and past (my prefered method if more complex)
or to just export an entire table

Rather than running this save it as a DTS package

Once you have this package you can schedule it to run at any time you wish

Or use triggers within SQL to call based on certain criteria

Or use VB to auto execute it based on specific criteria (i.e. text file being there)

Something like - and this is just a small section, also I am not sure of you vb skills:

Sub ExecuteADTSPackage
  Set oPKG = Createobject("DTS.Package")
  oPKG.LoadFromSQLServer &quot;<Server Name>&quot;, , , 256, , , , &quot;<DTS Package>&quot;
  oPKG.Execute
  oPKG.UnInitialize
  Set oPKG = Nothing
End Sub

Hope this helps



Damian.