Generate the text report and create a folder based on month and save the file with current date using SQL Script

 We have a requirement that we need to extract the data from the SQL server table and save that text file with current date and store that file in the current month folder and if the folder is not available in this case we need to create current month folder automatically using SQL script and store that file in this folder.

Here we are using the “AdventureWorksDW2017” database.

Below is the SQL script to fetch the data from the table.  

Below is the script to extract the data from the table and create the text file and folder.

DECLARE @Folder_Path           VARCHAR(80),         @Folder_Base_Name      VARCHAR(100),         @Folder_Name           VARCHAR(500),         @Sql_File_Path         VARCHAR(4000),         @Execution_Result_Path VARCHAR(4000),         @Server_Name           VARCHAR(20),         @sql_String            VARCHAR(4000),         @Database_Name         VARCHAR(100),         @OLE_Folder_ID         INT,         @OLE_Source            VARCHAR(255),         @OLE_Des               VARCHAR(255),         @init                  INT,         @OLE_File_Sytem_Object INT;  SET @Folder_Path='J:\Product'; SET @Folder_Base_Name ='Product'; SET @Folder_Name = @Folder_Path + '\' + @Folder_Base_Name + '_'                    + CONVERT(VARCHAR(6), Getdate(), 112); SET @Sql_File_Path = 'J:\code\DimProduct.sql'; SET @Execution_Result_Path= @Folder_Name + '\' + 'DimProduct_resultset_'                             + CONVERT(VARCHAR(8), Getdate(), 112) + '.txt'; SET @Server_Name ='BAGESH\BAGESHDB'; SET @Database_Name ='AdventureWorksDW2017';  -- it will fail if OLE automation not enabled EXEC @init=Sp_oacreate   'Scripting.FileSystemObject',   @OLE_File_Sytem_Object out  IF @init <> 0   BEGIN       EXEC Sp_oageterrorinfo         @OLE_File_Sytem_Object        RETURN   END -- check if folder exists EXEC @init=Sp_oamethod   @OLE_File_Sytem_Object,   'FolderExists',   @OLE_Folder_ID out,   @Folder_Name --if @OLE_Folder_ID =1 it means folder is exist if @OLE_Folder_ID=0 folder is not exist -- if folder doesnt exist, create it IF @OLE_Folder_ID = 0   BEGIN       EXEC @init=Sp_oamethod         @OLE_File_Sytem_Object,         'CreateFolder',         @OLE_Folder_ID out,         @Folder_Name   END -- in case of error, raise it  IF @init <> 0   BEGIN       EXEC Sp_oageterrorinfo         @OLE_File_Sytem_Object,         @OLE_Source out,         @OLE_Des out        SELECT @OLE_Des = 'Could not create folder: ' + @OLE_Des        RAISERROR (@OLE_Des,16,1)   END  EXECUTE @init = Sp_oadestroy   @OLE_File_Sytem_Object SET @sql_String = 'SQLCMD -E -S ' + @Server_Name + ' -d '                   + @Database_Name + ' -i ' + @Sql_File_Path + ' -o '                   + @Execution_Result_Path EXEC master..Xp_cmdshell   @sql_String

 We are creating the SQL Server job to run automatically.  

In this folder there are no any folders and files.

Learn here how to create SQL server Jobs in SQL Server Agent:

https://bageshkumarbagi-msbi.blogspot.com/2018/12/creating-new-job-in-sql-server-agent.html

Let’s run this job first time.

  

Job executed successfully.

  

Let’s see the report.

Folder created successfully.

  

In this folder file is created.   

File and file data.

Next day run 

Next month run. 

Now folder created with current year with month.

  

File with file data.

We got the expected result.

Happy Learning!  J