Create folder and copy file using xp_cmdshell

Hello All,

I have to copy file daily from SQL job to the file server. The SQL job should create a folder first day of each month as 'Jan', 'Feb' etc.. and upload the file accordingly.

If it's the last day of the month then it should create year folder '2012' and then month folder 'Jan' then copy the output of select table.

How do I do this in xp_cmdshell? Can anyone give me a example or sample code?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 you can create folder & copy files like this :

exec master..xp_cmdshell 'mkdir c:\test2' , no_output       -- For creating folder

master.dbo.xp_cmdshell 'copy c:\test\testfile.txt c:\test2\'  -- for copying file

BrookKAuthor Commented:
Thanks mimran18.

But how do I check if it's first day of the month and then only create a folder and if first month then create 2 folders first for year and inside it is month.

This will check to see if a folder is created and if not, create it.
CREATE TABLE [dbo].[#tbl_temp_Timestamp](vchFileData varchar(2000))
INSERT INTO #tbl_temp_Timestamp
  EXEC [master].[dbo].xp_cmdshell 'dir \\Server\Share\*. /T:W '

DECLARE @FolderName varchar(7) = cast(datepart(YEAR,getdate()) as varchar(4)) + datename(month,getdate())
DECLARE @ShellCmd varchar(MAX) = 'exec master..xp_cmdshell ''mkdir \\Server\Share\ + @FolderName + ', no_output'''
IF NOT EXISTS (SELECT * FROM [#tbl_temp_Timestamp] WHERE #tbl_temp_Timestamp.vchFileData LIKE '%' + @FolderName)
	EXECUTE (@ShellCmd)
DROP TABLE [#tbl_temp_Timestamp]

Open in new window

I generally prefer to use numeric months to keep the directories easily sorted chronologically.  ie 2011-12

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

BrookKAuthor Commented:
I get an error in the #table saying
'The system cannot find the file specified.'
For -
INSERT INTO #tbl_temp_Timestamp (vchFileData)
  EXEC [master].[dbo].xp_cmdshell 'dir \\Na-all-pr-bs02\sql_bak\SOX Audit\2011'

Try doing the EXEC by itself.  If it doesn't work, the rest won't.  You need to have appropriate permissions in the folder you are looking at.
Jason YousefSr. BI  DeveloperCommented:
Why you don't use SSIS to do that?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrookKAuthor Commented:
I am using SSIS package to do it. My criteria has been changed since this post.

The export files need to have centain naming conventions.

All the files should be dumped in a folder named:

The SSIS exports multiple files so each file should be named as:

Can someone help me on how to do it in SSIS package?

BrookKAuthor Commented:
Implemented my solution in SSIS
Jason YousefSr. BI  DeveloperCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.