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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Batch rename files with table data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

benking9987
Posting Yak Master

124 Posts

Posted - 04/20/2015 :  17:21:47  Show Profile  Reply with Quote
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 - 04/21/2015 :  13:39:36  Show Profile  Reply with Quote
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.

Go to Top of Page

benking9987
Posting Yak Master

124 Posts

Posted - 04/21/2015 :  18:46:27  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000