If from a DTS package, you need to combine 2 files to make one, which is the
better, more secure way to do it from the following 3 methods, and why?

1) Have an Sql Task that performs the following:
exec master.dbo.xp_cmdshell 'copy c:\temp\file1.dat+c:\temp\file2.dat
c:\temp\final.dat', NO_OUTPUT

Or

2) Have a copy.bat that contains 'copy c:\temp\file1.dat+c:\temp\file2.dat
c:\temp\final.dat' and execute it as a Win32 process in an Execute Process
Task

Or

3) Have VBScript in an ActiveX Script Task that contains the following?

' Read input files
InputFile1 = "c:\temp\file1.dat"
InputFile2 = "c:\temp\file2.dat"
OutputFile = "c:\temp\final.dat"
str_InputFile1 = ReadTextFile(InputFile1)
str_InputFile2 = ReadTextFile(InputFile2)

str_Output = str_InputFile1 & vbcrlf & vbcrlf & str_InputFile2
str_Output = str_Output & vbcrlf & vbcrlf

' Write output file
WriteToFile OutputFile,str_Output

End Function

' Functions
Function WriteToFile( str_FileName, str_Message )
Set obj_FSO = CreateObject("Scripting.FileSystemObject")

Set obj_OutputFile = obj_FSO.OpenTextFile(str_FileName, 2, True, 0)
obj_OutputFile.WriteLine(str_Message)
obj_OutputFile.Close
Set obj_OutputFile = Nothing
Set obj_FSO = Nothing
End Function

Function ReadTextFile( str_FileName )

Set obj_FSO = CreateObject("Scripting.FileSystemObject")
Set obj_InputFile = obj_FSO.GetFile(str_FileName)
Set obj_TextStream = obj_InputFile.OpenAsTextStream(1, TristateFalse)
str_Text = obj_TextStream.ReadAll
Set obj_InputFile = Nothing
Set obj_FSO = Nothing
ReadTextFile = str_Text

--

Raymond Lewallen

It would depend on who has access to the DTS to change / execute it, and on
what computer it runs on.
But, I'd say that using xp_cmdshell requires you to be a member of the
sysadmin role, so the account that executes this (successfully) can do
anything they want on the server. So it wouldn't be a good idea to run this
under the account of semi-trusted personnel.


"Raymond Lewallen" wrote:

The package is owned by a sql account with no assigned server roles, and has
a public role in the database in which the task has a connection to. The
package executes via a scheduled job, in which a windows user account with
access to the directories to read/write the files, is the same account the
Sql Server Agent service is running under.

A non-sysadmin user can execute xp_cmdshell by impersonating the Sql Server
Agent Proxy account specified for the server.

--
Raymond Lewallen