mssqltips logo

Rename and Move a File with PowerShell in a SQL Server Agent Job

By:   |   Updated: 2014-04-21   |   Comments (11)   |   Related: More > PowerShell

Problem

We import a file once a day and after importing the file, we need it moved to an archive folder with a date stamp appended to the file name.  We have had trouble in the past with this process, so we'd like this to run as its own step to help troubleshoot it if there are issues.

Solution

Click here for video version!

We can find a multitude of solutions for this problem, but the key is to make it its own process so that with process of elimination we can determine what part of our job failed. PowerShell makes it easy to complete a task like this because the jobs steps in SQL Server Agent allow us to use PowerShell.

PowerShell code to Move and Append Date to File Name

First, we want to obtain today's date. In PowerShell, the command below will obtain it and store it in the variable $d:

$d = Get-Date -uFormat "%Y%m%d"

Second, we want to rename the current file and append the date to the filename. For testing purposes, let's create a text file called "test" and put it on the "C" drive under the folder "files" and finally create a folder called "Archive" within the "files" folder. To make this into a function later, we'll break this up piece-by-piece, creating separate variables that will later become parameters. Let's create the variable $location, which will store the drive letter and folder location of the file, $file, which will store the file name, and $extension, which will store the extension. From there, let's create two new variables $old, which will hold the full file path of the file, and $new which will hold the full file path of the file after the date has been appended to the file name. Note that $new adds an underscore and the date ("_" + $d):

$date = Get-Date -uFormat "%Y%m%d"
## These will become parameters in our function later
$locationPath = "C:\files\"
$fileName = "test"
$extension = ".txt"
$old = $locationPath + $fileName + $extension
$new = $locationPath + $fileName + "_" + $date + $extension
Rename-Item $old $new

The result is that "C:\files\test.txt" becomes "C:\files\test_03242013.txt" (or whatever today's date is when you're reading this). Now we can add another variable ($archive), which will later become a parameter for our function, and another command (Move-Item) to archive the file:

$date = Get-Date -uFormat "%Y%m%d"
$locationPath = "C:\files\"
$fileName = "test"
$extension = ".txt"
$old = $locationPath + $fileName + $extension
$new = $locationPath + $fileName + "_" + $date + $extension
$archive = $locationPath + "Archive\"
Rename-Item $old $new
Move-Item $new $archive

The variable $archive adds the folder "Archive\" to the path "C:\files", and the Move-Item command moves the file. The final step is replacing this with a function, where we can re-use it for other files and job steps:

Function RenameMoveFile($locationPath, $fileName, $extension, $archiveFolder)
{
    $date = Get-Date -uFormat "%Y%m%d"
    $old = $locationPath + $fileName + $extension
    $new = $locationPath + $fileName + "_" + $date + $extension
    $archiveFolder = $locationPath + $archiveFolder + "\"
    Rename-Item $old $new
    Move-Item $new $archiveFolder
}
## This is the only part that we'd edit
RenameMoveFile -locationPath "C:\files\" -fileName "test" -extension ".txt" -archiveFolder "Archive"

As an final note, we never need to re-write this function again. We can now add it to a job step in SQL Server Agent, editing only the parameters as shown above in the last line of the code.

Summary

Although my purpose for writing this was to rename and move files for an import process, this can also be used for any number of things like moving and renaming backup files, moving and renaming export files, etc.  I hope this little script comes in handy.

Watch Video Version of Tip
Next Steps
  • Test the PowerShell script locally or in a development environment.
  • Ensure that your SQL Server Agent account has the appropriate permissions to execute PowerShell scripts.


Last Updated: 2014-04-21


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, October 02, 2014 - 9:41:35 AM - Chris Back To Top

Thanks Tim for the excellent PS script.

It works like a charm for me.

Now we've implemented Dynamics and now we have a batchjob that is putting out a lot of folders instead.

Tried to modify your script for use with folders but with no success at all! :-(

I can get a script to change one folder but only if I'm specifying the exact foldername (no wildcards accepted in PS?)

Should say that I'm by far is no scripting guru!

I could probably solve this using old cmd batchfiles but I think it's about time I get a hang on PS... :-D

 

Any hint & tips on how I get this to work on folders?

Catch the foldername, renaming it with added date and then move it to another location?

As there are multile folders coming out from the batchjob it would be nice to have it to rename and move them all.

 

Thanks in advance!

Best regards

Chris

 


Friday, August 29, 2014 - 10:00:27 AM - Tim Back To Top

@Bob

Using the above PowerShell function, we can loop through a folder called "OurFolder" and filter all the files that end in "txt" then call the function:

$locationPath = "C:\OurFolder\"
$files = Get-ChildItem $locationPath -Filter *.txt
foreach ($f in $files)
{
    RenameMoveFile -locationPath $locationPath -filename $f.BaseName -extension ".txt" -archiveFolder "Archive"
}

Now, if we were going to filter on CSVs, we would need to change accordingly.


Thursday, August 28, 2014 - 8:13:13 PM - Bob Back To Top

Hey Tim,

Thanks for writing up this great tip.  It's a nice option for copying files from one folder to another.  Is it possible to put this function into a control loop in order to copy or rename multiple files?  We have always had to use the "Execute SQL Task" task in SSIS to make copies of files for historical purposes with these properties:

Executable:   c:\Windows\system32\CMD.EXE

Arguments:   /c copy "E:\FolderName\Filename.txt" "E:\FolderName\History\Filename_%date:~10,4%-%date:~4,2%-%date:~7,2%.txt

We have to use one task per file and that list can be pretty long sometimes, so putting your code in a loop would be very useful.

Cheers,

Bob.


Wednesday, April 30, 2014 - 11:41:40 AM - Tim Back To Top

@iidiri  I'd definitely suggest upgrading to PowerShell 3.0 (see here: http://blogs.technet.com/b/heyscriptingguy/archive/2012/09/06/powershell-3-0-is-now-available-for-download.aspx).  From the error you posted, it appears that in the line calling the parameters, there's a period somewhere (at least the error indicates that).  With that function in the comment, when called it should look like

RenameMoveFile -locationPath $locationPath -fileName $filename -extension $extension -archiveFolder $archiveFolder

 


Tuesday, April 22, 2014 - 2:28:23 PM - iidiri Back To Top

Hello Tim,

Please see below:

Name             : ConsoleHost

Version          : 2.0

InstanceId       : 38a6e341-b0fb-4623-ad68-f0325e36f127

 

UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface

CurrentCulture   : en-US

CurrentUICulture : en-US

PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy

IsRunspacePushed : False

Runspace         : System.Management.Automation.Runspaces.LocalRunspace

 

When executing the NEW function,  I am getting an error see below.

 

 

 

 

Missing property name after reference operator.

At line:16 char:57

+ RenameMoveFile -locationPath $locationPath -fileName $f. <<<< ..

    + CategoryInfo          : ParserError: (.:String) [], ParentContainsErrorR

   ecordException

    + FullyQualifiedErrorId : MissingPropertyName

 

Thank you, for you time.

 

Idir,

 

 

 


Tuesday, April 22, 2014 - 1:34:28 PM - Tim Back To Top

@iidiri In ISE, what version of PowerShell does it return (type $Host.Version)?  If you're in Version 2, try the below script in ISE:

 ## This is the only part that we'd edit
Param($locationPath = "C:\files\"
    , $filename = "test"
    , $extension = ".txt"
    , $archiveFolder = "Archive"
)
Function RenameMoveFile
{
    Param([string]$locationPath, [string]$fileName, [string]$extension, [string]$archiveFolder)
    $date = Get-Date -uFormat "%Y%m%d"
    $old = $locationPath + $fileName + $extension
    $new = $locationPath + $fileName + "_" + $date + $extension
    $archiveFolder = $locationPath + $archiveFolder + "\"
    Rename-Item $old $new
    Move-Item $new $archiveFolder
}
RenameMoveFile -locationPath $locationPath -fileName $filename -extension $extension -archiveFolder $archiveFolder

Monday, April 21, 2014 - 4:24:11 PM - iidiri Back To Top

Hi Tim,

Thank you for getting back to me. I did another round of testing in both ISE and SQL Agent the problem occurs only when using the function. I tried the other two scripts and they worked fine. It looks like the call from RenameMoveFile -locationPath "C:\files\" -fileName "test" -extension ".txt" -archiveFolder "Archive"

does not trigger the function to execute and return the values properly. Unless I am doing something wrong!

Thank you.


Monday, April 21, 2014 - 2:24:33 PM - Andreas Back To Top

to elaborate my comment from earlier this morning:

I usually don't put the Powershell code directly into the Agent-Steps, I use a commandshell and call the powershell.exe from there, with the name of the actual script as parameter. This considerably increases re-use of functionality, according to the DRY principle.

And within the powershell script, I use code like this:

 

try{

... your logic here

}
catch
{
 write-host $_.Exception.Message;
 [Environment]::Exit("1");
}

This raises an error that the agent job will recognize and therefore fails the job step. In the error description you then see the actual message, indicating what went wrong.


Monday, April 21, 2014 - 1:26:55 PM - Tim Back To Top

@Andreas - Yes, appreciate the feedback - excellent point!

@iidiri - Thanks for testing the script.  I double checked on an environment with PowerShell 3.0 (you can check your version by entering $Host.Version in PowerShell) and SQL Server 2012, so I checked out Microsoft's site to see if 2008R2 allows PowerShell scripts in SQL Server agent (here: http://technet.microsoft.com/en-us/library/cc281962(v=sql.105).aspx) and I don't see any indication that they prevent it.  Could you check it without SQL Server Agent, just to make sure everything is right - copy and paste in the PowerShell ISE scripting window?


Monday, April 21, 2014 - 11:57:09 AM - iidiri Back To Top

Hi Tim,

The concept is good but not working. I tried your function above on SQL 2008 R2 and it didn't work. I copied the function to a newly created job with one step (the function). the job executes successfully, but the test.txt file was not moved to the "Archive" folder or renamed.

 

Idir,

 

 


Monday, April 21, 2014 - 8:49:13 AM - Andreas Back To Top

one thing i would add to the code is a try/catch around the code and the proper return of an error code, which you can then read in SQL Agent. otherwise you may not notice when the powershell script fails for whatever reason



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools