I want a DTS packag ethat will query a table and, based on a couple of conditions, create several text files.

For instance, let's say that the fields in the table are Date, Server Name and User. I want to create a separate text file for each server/date combination that lists all the users for that server/date combination. There's no fixed number of servers or dates, so the number of files generated might be different every time.

How does one go about doing this with DTS on SQL Server 2000? Thanks.

Jerry

I would use a vbscript that calls a stored proc then loops through the recordset and creates text files with the File System Object and writes the data to them. Just keep checking the server/date and when it changes start writing to a new file. See the post from yesterday "Import text file to multiple tables" for a bunch a examples. You are doing something like the reverse of what he is doing. If you have any coworkers that do VB or VBA this would be fairly easy and not even need to run in a DTS package if you choose just to execute a vbscript instead. If you want it in a DTS then put the code in an activeX script task.

The benefit of using vbscript is that you have total control of what is happening and whether you are working in sql server, Excel, VB, active server pages, etc., the vb syntac is pretty much the same.

Walter
<rlrcstr (AT) newsgroups (DOT) nospam> wrote

I want a DTS packag ethat will query a table and, based on a couple of conditions, create several text files.

For instance, let's say that the fields in the table are Date, Server Name and User. I want to create a separate text file for each server/date combination that lists all the users for that server/date combination. There's no fixed number of servers or dates, so the number of files generated might be different every time.

How does one go about doing this with DTS on SQL Server 2000? Thanks.

Jerry

I had a few minutes so I put a script together. This isn't tested or anything and might have some syntax/logic issues but it should be a good start:


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB;Trusted_Connection=yes; Data Source=servername; Initial Catalog=database_name"
set objCmd = CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objConn
objCmd.CommandText = "usp_getMyData" 'you're stored procedure or sql statement
set objRst = CreateObject("ADODB.Recordset")
objRst.Open objCmd

set fso = CreateObject("Scripting.FileSystemObject")
str_user = "-99"

if objRst.IsOpen then
'create first file
fso.CreateFile(path & firstfilename)'you will need to supply these
thefile = fso.GetFile (path & firstfilename)
do while not objRst.eof

'create new file when user changes
if objRst("user") <> str_user and str_user <> -99 then
fso.createFile (path & second file name) 'this will have to be a variable since you won't know how many files are needed
thefile = fso.getfile(path & seconf file name)

'note you can choose to write a header row with column names at this point if you want to.
end if

str_line = objrst("server") & "|" & objRst("date") & "|" & objrst("user") 'use a pipe to delimit or whatever else works

thefile.WriteLine str_line

str_user = objrst("user")

objRst.MoveNext

loop
end if

objRst.close
set objRst = nothing
ojbConn.close
set objConn = nothing
set objCmd = nothing
'enf of file
<rlrcstr (AT) newsgroups (DOT) nospam> wrote

I want a DTS packag ethat will query a table and, based on a couple of conditions, create several text files.

For instance, let's say that the fields in the table are Date, Server Name and User. I want to create a separate text file for each server/date combination that lists all the users for that server/date combination. There's no fixed number of servers or dates, so the number of files generated might be different every time.

How does one go about doing this with DTS on SQL Server 2000? Thanks.

Jerry

Much appreciated... I'll work with this.
"Walter Mallon" <waltmallon (AT) yahoo (DOT) com> wrote

I had a few minutes so I put a script together. This isn't tested or anything and might have some syntax/logic issues but it should be a good start:


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB;Trusted_Connection=yes; Data Source=servername; Initial Catalog=database_name"
set objCmd = CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objConn
objCmd.CommandText = "usp_getMyData" 'you're stored procedure or sql statement
set objRst = CreateObject("ADODB.Recordset")
objRst.Open objCmd

set fso = CreateObject("Scripting.FileSystemObject")
str_user = "-99"

if objRst.IsOpen then
'create first file
fso.CreateFile(path & firstfilename)'you will need to supply these
thefile = fso.GetFile (path & firstfilename)
do while not objRst.eof

'create new file when user changes
if objRst("user") <> str_user and str_user <> -99 then
fso.createFile (path & second file name) 'this will have to be a variable since you won't know how many files are needed
thefile = fso.getfile(path & seconf file name)

'note you can choose to write a header row with column names at this point if you want to.
end if

str_line = objrst("server") & "|" & objRst("date") & "|" & objrst("user") 'use a pipe to delimit or whatever else works

thefile.WriteLine str_line

str_user = objrst("user")

objRst.MoveNext

loop
end if

objRst.close
set objRst = nothing
ojbConn.close
set objConn = nothing
set objCmd = nothing
'enf of file
<rlrcstr (AT) newsgroups (DOT) nospam> wrote

I want a DTS packag ethat will query a table and, based on a couple of conditions, create several text files.

For instance, let's say that the fields in the table are Date, Server Name and User. I want to create a separate text file for each server/date combination that lists all the users for that server/date combination. There's no fixed number of servers or dates, so the number of files generated might be different every time.

How does one go about doing this with DTS on SQL Server 2000? Thanks.

Jerry