June 9, 2008 at 5:24 am
Hi all,
I wanted to get some help with a personal project, with the dual purpose of practicing DTS.
On one of my drives I've got a master folder (Pictures) containing 56,000 jpg's. The folder is divided by several layers of sub folders, and at the bottom layer, I have a sub-sub-sub(etc) folder containing the jpg's.
Now, only that lowest layer of subfolder is descriptively titled, ie "Big Ben", and thus it will contain pictures of Big Ben! The reason for the renaming is that in most folders, the pictures are unhelpfully titled, "Picture1.jpg", "Picture2.jpg" etc.
What I'd like is to create a package that renames each picture in a subfolder to that subfolders name, thus "Big Ben1.jpg", "Big Ben2.jpg" etc. then copies the 56,000 newly-renamed files to a single folder (on the same drive).
Obviously I'll back up the collection before allowing DTS to begin the renaming.
Thanks in advance,
Jaybee.
June 10, 2008 at 7:05 am
I suppose you could use an ActiveX script in DTS, or you could just write the code in notepad and rename it .vbs. What I'd recommend of course is a VBScript to perform this. Check out the following link:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/oct04/hey1020.mspx
It will give you a quick primer on how to recursively go through all the folders and subfolders. There are also plenty of links to other examples and tutorials on VBScript. You can use the script to change the name of the files and if you have a counter that you reset for each folder I think you'll be set. It shouldn't take long to run. I would definitely recommend backing up your folders though since you might need to take a few stabs at it to get it right.
June 10, 2008 at 7:37 am
This is really a scripting problem. You can host this in DTS, but you'll be writing VBScript that can be run from the command line as easily as tucked inside a script component. DTS doesn't really help here, but the scripting skills you develop will be useful in other projects, and some of the same techniques will make sense in Powershell, which is where you'll want to move for SQL Server 2008 and above.
The FileSystemObject is your friend. Use it to build a collection of folders, then loop through building more collections. Keep the name of the folder and then use that to change the name of the individual files.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply