Best Way To Handle File I/O From SQL
-
I have been tasked with finding an alternative method to using xp_CmdShell for doing some basic I/O with a single file. Specifically I need to move a file from it's current location into a sub-directory of that same location and also change the name of the file. Why? The file (csv) contains records for an import and when the import is done the file needs to be moved to a sub-directory and renamed. The importing of the data in the csv file is already being handled by native functionality within trhe product I support so I need only worry about the file moving/renaming. Orginally our custom dev staff same up with a SP that calls xp_CmdShell to move & rename the csv file. For security reasons the use of xp_CmdShell is a big no no. When management realized the solution developed by programming for our client could not be implemented, they asked the IT group that manages the servers, both the DB serves as well as Web & Files Servers where we host the clients data along with our application, what alterntiaves they suggest. DTS was recomended as the alterntaive to use. Since I am the only one with some knowledge of DTS I was aksed to look into this. That's how I got involved.
Now that I look at this I'm not so sure that DTS is the best route and so I was hoping to solicit so input from some more experienced users out there. I have found what I believe are a couple of different options on how to handle the file move & rename.
1) DTS Package
2) sp_OA... SP's (instantiationg the FileSystemObject)
The reason xp_CmdShell can't be used is because of the permissions it requires and the hole in security that it opens.
Does anyone have any feedback on either of the above 2 solutions and or this whole process in general, that is how best to handle file operations from with SQL.
Thanks
Kindest Regards,
Just say No to Facebook! -
This has got to be a first for me. It's been 24 hours since I posted the above item and I have yet to recieve a single response. Am I posting to the wrong forum for this question? Does no one out there have any thoughst on the above?
Kindest Regards,
Just say No to Facebook! -
Yeah, you could just write some VBScript and use it in a DTS package to move and re-name the file. That's the route that I would go. Thanks.
Chris
-
Christopher,
Kindest Regards,
Just say No to Facebook! -
You're welcome.
-
I always use DTS with an ActiveX script and the FileSystemObject to move/delete/rename files. Moving files first then renaming (adding the current datetime), works every time for me.
John
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
You could also create a batch file in DOS and call it from scheduled tasks, in this batch file add the date to the file name and copy it to your folder....... eg .........
rem COPY.BAT
rem CREATE VARIABLE TO ATTACH DATE TO OUTPUT FILE
For /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^|cmd /q /k"prompt $D $T"') do (
For /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
set dow=%%i
set %%a=%%j
set %%b=%%k
set %%c=%%l
)
)
For %%i in (dow dd mm yy) do set %%i
ECHO %dd%%dow%%mm%
rem COPY FILE TO YOUR FOLDER
COPY filename.out C:\YourFolder\%dd%%dow%%mm%_filename.out
-
Patrick,
Calling batch files from scheduled jobs is using CmdExec.
Martin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply