Batch rename files with table data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Batch rename files with table data

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2015-04-20 : 17:21:47

I have a rename SSIS package I need to create. I have the process down for moving files between folders, but I need to add a complication wherein I name the source file with data coming from my SQL server. Here's an example:

I have 5 files in a folder:

20150420031600.pdf
20150420031610.pdf
20150420031620.pdf
20150420031630.pdf
20150420031640.pdf

My system now knows how to pick up these individual file names and then assign them to the particular ORDER NUMBER that they belong to within SQL. The data looks something like this:

File Name Order Number

20150420031600.pdf A1234
20150420031610.pdf B5678
20150420031620.pdf C5548862-45684
20150420031630.pdf D445521-CG5-456654
20150420031640.pdf E12222354

Now I need a process that will rename the existing PDFs into their corresponding order numbers like this:

20150420031600.pdf would become A1234.pdf
20150420031610.pdf would become B5678.pdf
20150420031620.pdf would become C5548862-45684.pdf
20150420031630.pdf would become D445521-CG5-456654.pdf
20150420031640.pdf would become E12222354.pdf

Any ideas on how to perform this? I'm sure I'll need to just flow the data from the SQL tables into variables, but I'm not clear how to get rolling on this.

Thanks in advance!

benking9987
Posting Yak Master

124 Posts

Posted - 2015-04-21 : 13:39:36

I think I've figured out how to change file names using the exec master..xp_cmdshell function within a T-SQL statement. Now, if I have the following select statement, I can generate all the individual command lines needed to change all the files in a particular folder. However, now I need to see if there is a way to RUN each statement generated by my SELECT statement in an execute SQL task.

Here's the SELECT statement that generates the commands:

SELECT 'exec master..xp_cmdshell '+ CHAR(39) + 'ren ' + CHAR(34) + 'folderlocation' + table.unconvertedfile + CHAR(34) + ' ' + table.ordernumber + '.pdf' + CHAR(39) FROM table WHERE unconvertedfile IS NOT NULL

Any ideas on how I can store the resulting select statements into a variable or something and then run each of the statements as T-SQL statements?

Thanks in advance.

benking9987
Posting Yak Master

124 Posts

Posted - 2015-04-21 : 18:46:27

I ended up figuring out how to do this using a For Loop container within SSIS and defining different variable components to the entire string and then looping through all the processes until the job was finished. I have to say it works pretty flawlessly. Hit me up if anyone wants to know the details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources