Solved

DTS Package to copy .txt file to another folder

Posted on 2008-10-01
15
1,474 Views
Last Modified: 2013-11-30
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
Comment
Question by:IEHP1
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 22616678
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
 
LVL 4

Assisted Solution

by:ThorSG1
ThorSG1 earned 100 total points
ID: 22619772
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
 

Author Comment

by:IEHP1
ID: 22625529
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
 

Author Comment

by:IEHP1
ID: 22626459
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
 

Author Comment

by:IEHP1
ID: 22626659
Also would like the renamed file to look like this:  1001_1226_MEMBER2Test.dat
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
ID: 22635525
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22635591
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:IEHP1
ID: 22636219
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22636401
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
 

Author Comment

by:IEHP1
ID: 22636703
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
 

Accepted Solution

by:
IEHP1 earned 0 total points
ID: 22637177
Solved my problem. Created batch file and repeated REM lines for each file.  Thanks everyone.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22637232
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22637299
Happy you got it working, and did find a missing \ in the destination filename (after historical) in posting ID:22636219
0
 

Author Comment

by:IEHP1
ID: 22637349
Yes I added the \ after I posted the results and it still gave me the same results.  Thanks though
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22639255
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now