Solved

DTS Package to copy .txt file to another folder

Posted on 2008-10-01
15
1,465 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Solved my problem. Created batch file and repeated REM lines for each file.  Thanks everyone.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

16 Experts available now in Live!

Get 1:1 Help Now