Robocopy to Delete File Older than Two Weeks and with Timestamp in Log File Name

In a batch file:
::Include datetime format yyyy-mm-dd_hhmmss in log filename
set dt=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%
set dt=%dt: =_%
set tm=%time:~0,2%%time:~3,2%%time:~6,2%
set tm=%tm: =0%
set Now=%dt%_%tm%
IF NOT EXIST \\src\empty_daily mkdir \\des\empty_dummy
Robocopy \\src\Filefolder \\des\empty_dummy *.* /s /MOVE /MINAGE:14 /W:5 /R:5 /log+:E:\log\%Now%_log.txt
rmdir \\des\empty_dummy/s /q

Another way in a batch file:
::Include datetime format yyyymmddhhmmss in log filename

For /F “Tokens=1-7 Delims=/:.- ” %%d In (“%Date%-%Time%”) Do Set Now=%%g%%e%%f%%h%%i%%j
IF NOT EXIST \\src\empty_daily mkdir \\des\empty_dummy
Robocopy \\src\Filefolder \\des\empty_dummy *.* /s /MOVE /MINAGE:14 /W:5 /R:5 /log+:E:\log\%Now%_log.txt
rmdir \\des\empty_dummy/s /q

https://gallery.technet.microsoft.com/scriptcenter/0595e5d5-184c-44ab-847e-056c2db3c253

Advertisements

Pass SSIS Package Parameter Values From Agent Job to Run a bat File with These Variables.

1. bat file
Design a bat file to use robocopy with three parameters for MINAGE and two file names with wildcards.
The parametera are used as percentage sign and a number starts at 1, like %1 %2 and %3.

2.SSIS package
Design an SSIS package to pass variables to the bat file

Create three SSIS variables: SSIS tab >> create one int32 (varNum) and two String variables (varDBName1 and varDBName2) and assign some default values if you choose to.
Next step to connect these variables to your bat file.

Use Execute Process Task:
From Execute Process Task Editor Process tab:
Execute—-E:\X\temp\mybatfilewiththreevariables.bat
Argument leave it blank and use the Expression tab to add argument for the string of these variables in a concatenation syntax.
WorkingDirectory—E:\temp\
StandardInputVariable — pick any variable as a first one

From Execute Process Task Editor Expression tab:
Expand Expression tab >>Property Expressions Editor from Property column, pick Arguments, then expand Expression tab (click on … tab>> Expression Builder window pop up.
link all variables as a string in this manner:
(DT_WSTR, 10) @[User::varNum]+” “+(DT_WSTR, 10) @[User::varDBName2]+” “+(DT_WSTR, 10) @[User::varDBName2]

Cast variable to datatype DT_WSTR and concatenate them with +” “+ to become a string with space separate them.
You can click on Evaluate Expression at the bottom and will see the Evaluated value from your default values.
Close all open windows and save your package.

3. Agent job

Create and agent job to run the SSIS package with three parameters to pass to a bat file to use robocopy inside the bat file.
@command=N’/FILE “\”E:\packages\passmultipleSSISParameterValuesInAgentJob.dtsx\”” /CHECKPOINTING OFF /SET “\”\Package.Variables[User::varNum]\””;6 /SET “\”\Package.Variables[User::vardbname1]\””;”\”\”\”myDb1\”\”\”” /SET “\”\Package.Variables[User::vardbname2]\””;”\”\”\”mydb2\”\”\”” /REPORTING E’,

Double click on AgentJob name >> from Job Properties window>>Click on Steps from left >> Click on Edit >> Job Step Properties >> click Set values tab >>
Add Property Path and value as below for each variable:
For integer parameter
Property Path: \Package.Variables[User::varNum]
Value: 1
For string type parameter, the Value needs double quotation around the string.
Property Path: \Package.Variables[User::varNum]
Value: “varDBName”

4.Check the bat file and an example

Retrieve these parameters from your bat file in the syntax %1 %2 %3 and use them in the place to replace static value.
An example to use these three parameters to use robocopy to copy and clean files:

IF NOT EXIST \\desfolder\empty mkdir \\desfolder\empty
Robocopy \\src \\desfolder\empty /s /MOVE /MINAGE:%1 /LOG+:E:\log\%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%_mylog.txt
rmdir \\desfolder\empty /s /q
Robocopy \\src \\desfolder *%2*.* *%3*.* /s /z /np /MT:32 /W:5 /R:5 /log+:E:\log\%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%_mylog.txt


Dynamic Unpivot and Pivot with Case

  

 
  
create table test (emp_id int 
,[17-Jul] decimal(6,2) 
,[17-Aug] decimal(6,2)
,[17-Sep] decimal(6,2) 
,[17-Oct] decimal(6,2) 
,[17-Nov] decimal(6,2) 
,[17-Dec] decimal(6,2)
,[18-Jan] decimal(6,2) 
,[18-Feb] decimal(6,2) 
,[18-Mar] decimal(6,2) 
,[18-Apr] decimal(6,2) 
,[18-May] decimal(6,2) 
,[18-Jun] decimal(6,2))

 Insert into test 
 values(597, 0,0,0,84.01,0,0,0,0,0,95.13,0,0)


 select * from test
 Declare @sqlUnpivot as NVarchar(4000)
  Declare @sqlPivot as NVarchar(4000)
Declare @ColsUnpivot as NVarchar(4000)=null
Declare @ColsPivot as NVarchar(4000)=null
 
 
 
Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+','  + QUOTENAME(COLUMN_NAME) +','+  Cast(ORDINAL_POSITION as varchar(3)) +')'
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'

  Select @ColsPivot =  COALESCE(@ColsPivot + ', ', '')  + QUOTENAME(COLUMN_NAME)  
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'
 
 
Select @sqlUnpivot='Select
emp_id,Col, Nullif(YY_MMM,0) YY_MMM, ORDINAL_POSITION
into temp
FROM test t
CROSS APPLY (Values ' + @ColsUnpivot + '  )  d(Col, YY_MMM,ORDINAL_POSITION) '
exec (@sqlUnpivot)
--print @sqlUnpivot
  
Select @ColsPivot = STUFF( (SELECT  ',' + 'Max(CASE WHEN Col=' + quotename(Col,'''') + ' THEN [YY_MMM] else 0 end ) as ' + quotename(Col,'[')  + char(10)+char(13)
                             FROM temp
							 order by ORDINAL_POSITION
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sqlPivot  =' Select emp_id,'+   @ColsPivot + ' from 
( SELECT emp_id, Col, ORDINAL_POSITION, 
CAST(SUBSTRING(MAX( CAST(ORDINAL_POSITION AS BINARY(4)) + CAST(Nullif(YY_MMM,0) AS BINARY(8)) )
OVER( ORDER BY ORDINAL_POSITION ASC ROWS UNBOUNDED PRECEDING ),5,8) AS Decimal(6,2) ) [YY_MMM]
FROM temp) t Group by emp_id    ';
     
--print @sqlPivot
EXEC(@sqlPivot)

--clean up temp table
 drop table temp


 drop table test