Solved

DTS Package to copy .txt file to another folder

Posted on 2008-10-01
15
1,481 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

838 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