• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1507
  • Last Modified:

DTS Package to copy .txt file to another folder

Hi Experts:
I'm currently using SQL 2000.  I'm attempting to create a DTS package that will copy a text file, created by another DTS, to a different folder.  The files I'm copying will be saved under the same name with the addition of today's date for historical purposes.  Below is the DTS Function I have written:

Function Main()
      
      Dim oFSO
      Dim SourceFile
      Dim DestinationFile
      Dim strtoday
      Dim strTime

      Set oFSO = CreateObject("Scripting.FileSystemObject")
      strtoday ="_"&left(now(),2) & right(left(now(), 5),2) & "_"
      strTime =right("0"&cstr(HOUR(TIME)),2)&right("0"&cstr(MINUTE(TIME)),2)

    SourceFile = "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\MEMBER2.dat"
    DestinationFile = "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\Historical\MEMBER2"&strtoday&strtime&".dat"
      
      oFSO.CopyFile SourceFile, DestinationFile
   oFSO.CopyFile "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\MEMBER2.dat",  "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\Historical\MEMBER2"&strtoday&strtime&".dat"

      Set oFSO = Nothing
      Main = DTSTaskExecResult_Success

End Function

When I run the DTS fromt he DTS section it runs with no issues, but when I create a JOB to run it, it fails at the following line:  oFSO.CopyFile SourceFile, DestinationFile.  I'be crated the job from the DTS section and I've also created the job manually using exec master..xp_cmdshell 'DTSRun /S IEHPSQL1 /E /N "HrlJobs"' but I get the same results.  

Can you give me an idea what I'm doing wrong or suggestions on how else to copy these files?

Thanks
0
IEHP1
Asked:
IEHP1
  • 7
  • 5
  • 2
  • +1
4 Solutions
 
Anthony PerkinsCommented:
That would be because the startup account for the SQL Server Agent service does not have access to the resources it requires to complete the task.  More than likely it is a System account.
0
 
ThorSG1Commented:
acperkins is correct the startup account not have permissions to the location of the file is the problem.

You can also use the Execute Process Task in DTS to run a batch file to do the move.  But again your startup account would need to have permissions to the location of the file.
0
 
IEHP1Author Commented:
Thank you both.  We checked over at our end and something is going on with security.  So of us can run the DTS package some cannot, no one can run jobs with the DTS.  We are begining to think it has to do with the Active X section of it.  Also checked permissions and all is ok.  So I decided to just create a batch process and calling it with a store procedure and then running the job.  That seems to work fine the only thing is can't figure out how to put the date and time in front of the file name.  Here's my SP:

CREATE PROCEDURE [CopyMEMFiles] AS

declare @sMOE varchar(150)
set @sMOE=convert(varchar(6),getdate(),112)

set @sMOE='c:\maxmc\cmbMEMCopyMemberFile.bat '
exec master..xp_cmdshell @sMOE
GO

Thanks for your help
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
IEHP1Author Commented:
This is the batch file I've written thus far and it works except I need the file name after the date and I also need the time so that it does not overwrite any previous saved files:

@echo off
setlocal
 
REM MEMBER2Test.dat
set file=\\Iehpcorp\maxmc\Data\MEM\HOURLY\member2test.dat
 
REM MEMBER2Test.dat
set newname=%date:~4,2%%date:~7,2%%date:~10,4%.dat
 
echo f|xcopy "%file%" "\\Iehpcorp\maxmc\Data\MEM\HOURLY\historical\%newname%" /C /H /R /Y

exit

Thanks

0
 
IEHP1Author Commented:
Also would like the renamed file to look like this:  1001_1226_MEMBER2Test.dat
0
 
Mark WillsTopic AdvisorCommented:
Ummmm.... if running xp_cmdshell, then do not need the batch job, just do the xcopy directly as a command that xp_cmdshell executes...

reaaly do not understand the 1001_1226 part of that file name when you say you want date - is that october 01 at 12:26 pm ?

CREATE PROCEDURE [CopyMEMFiles] AS

declare @sMOE varchar(150)

set @sMOE='xcopy \\Iehpcorp\maxmc\Data\MEM\HOURLY\member2test.dat  \\Iehpcorp\maxmc\Data\MEM\HOURLY\historical\%' + replace((right('00'+convert(varchar,getdate(),112),4) + '_' + left(convert(varchar,getdate(),114),5)),':','') + '_MEMBER2Test.dat'

exec master..xp_cmdshell @sMOE
GO
0
 
Mark WillsTopic AdvisorCommented:
Need to get rid of that rogue % sign - sorry about that, and do a print @smOE before trying to actually run it so you can see the full structure of the command that is about tp be launched by the xp_cmdshell...
0
 
IEHP1Author Commented:
1001_1226 is the month and date (10/01) and time (12:26).  I ran your process by creating a Stored procedure and then running the SP using a job and it ran with no errors BUT it did not copy the file to the new location.  This is my SP:

CREATE PROCEDURE [CopyMEMFiles2] AS
declare @sMOE varchar(150)

set @sMOE='xcopy \\Iehpcorp\maxmc\Data\MEM\HOURLY\MEMBER2Test.dat  \\Iehpcorp\maxmc\Data\MEM\HOURLY\historical' + replace((right('00'+convert(varchar,getdate(),112),4) + '_' + left(convert(varchar,getdate(),114),5)),':','') + '_MEMBER2Test.dat'

exec master..xp_cmdshell @sMOE
GO
0
 
Mark WillsTopic AdvisorCommented:
Could be a path problem - is that path available ? If using UNC is thaat share available ?  Try copying it to a local c:\ disk first and replace the unc path with c:\. then you will know it is the unc path and/or your SQL user cannot see that other machine...

0
 
IEHP1Author Commented:
Changed path to C:\ same results.  I did in all my testings create a batch job that gives me the results I'm looking for.  The only thing is that I need to copy multiple files, do you know if I need to create a batch file for each.  Below is my code:

@echo off
setlocal
 
REM MEMBER2test.dat
set file=\\Iehpcorp\maxmc\Data\MEM\HOURLY\MEMBER2test.dat
 
REM MEMBER2test.dat
set newname=%date:~4,2%%date:~7,2%_%TIME:~0,2%%TIME:~3,2%_MEMTest.dat

echo f|xcopy "%file%" "\\Iehpcorp\maxmc\Data\MEM\HOURLY\Historical\%newname%" /C /H /R /Y

Thanks for all your help.
0
 
IEHP1Author Commented:
Solved my problem. Created batch file and repeated REM lines for each file.  Thanks everyone.
0
 
Mark WillsTopic AdvisorCommented:
Hi, can understand that you want to use your batch job, ut, if xp_cmdshell cannot run a simple copy, then it probably is going to run a simple batch job either. It is a two-edged test...

What happens if you open a query window and simply run :

declare @sMOE varchar(150)

set @sMOE='copy c:\MEMBER2Test.dat  c:\' + replace((right('00'+convert(varchar,getdate(),112),4) + '_' + left(convert(varchar,getdate(),114),5)),':','') + '_MEMBER2Test.dat'

exec master..xp_cmdshell @sMOE


you should see some results / messages...  or even just do a DIR of that file...
0
 
Mark WillsTopic AdvisorCommented:
Happy you got it working, and did find a missing \ in the destination filename (after historical) in posting ID:22636219
0
 
IEHP1Author Commented:
Yes I added the \ after I posted the results and it still gave me the same results.  Thanks though
0
 
Anthony PerkinsCommented:
The point that you are missing is that all you have done is change the account used from the SQL Server Agent to the SQL Server service startup account.  That is the reason it is now "working"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now