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,
BrookKAsked:
Who is Participating?
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Why you don't use SSIS to do that?
0
 
mimran18Commented:
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

0
 
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.

Thanks,
Brook
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
lluddenCommented:
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

0
 
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'

0
 
lluddenCommented:
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.
0
 
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:
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
0
 
BrookKAuthor Commented:
Implemented my solution in SSIS
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.