asked on

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?

Thanks,

mimran18

Hi
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

BrookK

ASKER

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.

Thanks,
Brook

lludden

This will check to see if a folder is created and if not, create it.

SET NOCOUNT ON
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

BrookK

ASKER

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'

lludden

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.

ASKER CERTIFIED SOLUTION

Jason Yousef

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrookK

ASKER

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:
MCP_<Name of Server>_YYYYMMDDHHMMSS

The SSIS exports multiple files so each file should be named as:
MCP_ExpOrders_YYYYMMDDHHMMSS.CSV
MCP_PendingOrders_YYYYMMDDHHMMSS.CSV
etc..

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

Thanks,
-B

BrookK

ASKER

Implemented my solution in SSIS

Jason Yousef

Great SSIS is the way to go.....

check that post

http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html