14. EPMO Open Source Coordination Office Redaction File Detail Report

Produced by Araxis Merge on 11/9/2018 12:16:34 PM Eastern Standard Time. See www.araxis.com for information about Merge. This report uses XHTML and CSS2, and is best viewed with a modern standards-compliant browser. For optimum results when printing this report, use landscape orientation and enable printing of background images and colours in your browser.

14.1 Files compared

# Location File Last Modified
1 RAMS-2.3.0.zip\RAMS-2.3.0\ps CreateSSISJobs.ps1 Sun Jul 15 00:51:08 2018 UTC
2 RAMS-2.3.0.zip\RAMS-2.3.0\ps CreateSSISJobs.ps1 Fri Nov 9 14:47:25 2018 UTC

14.2 Comparison summary

Description Between
Files 1 and 2
Text Blocks Lines
Unchanged 4 302
Changed 3 8
Inserted 0 0
Removed 0 0

14.3 Comparison options

Whitespace
Character case Differences in character case are significant
Line endings Differences in line endings (CR and LF characters) are ignored
CR/LF characters Not shown in the comparison detail

14.4 Active regular expressions

No regular expressions were active.

14.5 Comparison detail

  1   function C reate-SSIS AgentJob {  
  2       <#
  3       
  4       .SYNOP SIS
  5           Cr eate SSIS  agent jobs
  6       
  7       .DESCR IPTION
  8           
  9       
  10       .PARAM ETER webSe rviceUrl
  11           [s tring] The  SSIS web  services U RL.
  12       
  13       .PARAM ETER repor tFolder
  14           [s tring] The  name of t he folder  on the SSR S server w here the r eports wil l be impor ted. "RAMS  Reports"  is the 
  15           de fault. 
  16       
  17       .PARAM ETER Agent JobName
  18           [s tring] The  name of t he folder  on the SSR S server w here the d ata source s will be  imported.  "Data Sour ces" is th
  19           de fault. 
  20       
  21       .PARAM ETER dtsxP ackage
  22           [s tring] The  base dire ctory of t he reports  directory  in the de ployment p ackage. Bu ilt using  the RAMS r elease 
  23           nu mber, e.g.  RAMS-2.3. 0, and add ing the re ports dire ctory. For  example,  D:\RAMS-2. 3.0\Report s.
  24       
  25       .NOTES
  26           Mo dified fro m http://m icrosoft-s sis.blogsp ot.com/201 6/10/using -powershel l-to-creat e-sql-agen t.html
  27       
  28       .EXAMP LE
  29             Create-SSI SAgentJob  -ssisServe r " DNS    S
" -dbServe r " DN S
" -reportF older "RAM S" -dtsxPa ckage "Ann ualReviewN otificatio n.dtsx" -p rojectName  "EmailNot ification"  -contentD BName "WSS _Content"  -ramsDBNam e "RAMS" - jobStartTi me (New-Ti mespan -ho urs 23 -mi nutes 00)
  30  
  31       .EXAMP LE
  32             Create-SSI SAgentJob  -ssisServe r " DN S     " -dbServe r " DN S
" -reportF older "RAM S" -dtsxPa ckage "Tra iningExpir ationNotif ication.dt sx" -proje ctName "Em ailNotific ation" -co ntentDBNam e "WSS_Con tent" -ram sDBName "R AMS" -jobS tartTime ( New-Timesp an -hours  23 -minute s 30)
  33       #>
  34       Param(
  35           [s tring]$ssi sServer,
  36           [s tring]$dbS erver,
  37           [s tring]$rep ortFolder  = "RAMS",
  38           [s tring]$dts xPackage,
  39           [s tring]$pro jectName,
  40           [s tring]$con tentDBName ,
  41           [s tring]$ram sDBName,
  42           [T imespan]$j obStartTim e
  43       )
  44       #Power Shell SSIS  JobStep
  45       ###### ########## ########## ######
  46       ###### #### PARAM ETERS #### ######
  47       ###### ########## ########## ###### 
  48       # Dest ination
  49       #$Ssis Server = $ webService Url
  50       #$repo rtFolder =  $reportFo lder
  51       #$Proj ectName =  $ProjectNa me
  52        
  53       # Job
  54       #$JobN ame = $age ntJobName
  55       #$dtsx Package =  $dtsxPacka ge
  56       #$JobS tartTime =  New-TimeS pan -hours  23 -minut es 00
  57        
  58       #clear
  59       write- log -messa ge "====== ========== ========== =======" - consoleFor eground Wh ite
  60       write- log -messa ge "==  Us ed paramet ers             ==" - consoleFor eground Wh ite
  61       write- log -messa ge "====== ========== ========== =======" - consoleFor eground Wh ite
  62       write- log -messa ge "SSIS S erver   :  $SsisServe r" -consol eForegroun d White
  63       write- log -messa ge "DB Ser ver     :  $dbServer"  -consoleF oreground  White
  64       write- log -messa ge "report Folder  :  $reportFol der" -cons oleForegro und White
  65       write- log -messa ge "Projec tName   :  $projectNa me" -conso leForegrou nd White
  66       write- log -messa ge "Job na me      :  $JobName"  -consoleFo reground W hite
  67       write- log -messa ge "dtsxPa ckage   :  $dtsxPacka ge" -conso leForegrou nd White
  68       write- log -messa ge "conten tDBName :  $contentDB Name" -con soleForegr ound White
  69       write- log -messa ge "ramsDB Name    :  $ramsDBNam e" -consol eForegroun d White
  70       write- log -messa ge "Schedu leTime  :  $JobStartT ime" -cons oleForegro und White
  71       write- log -messa ge "====== ========== ========== =======" - consoleFor eground Wh ite
  72       write- log -messa ge " " -co nsoleForeg round Whit e
  73       
  74       # Refe rence SMO  assembly a nd connect  to the SQ L Sever In stance 
  75       # Chec k the numb er in the  path which  is differ ent for ea ch version
  76       Add-Ty pe -Path ' C:\Program  Files\Mic rosoft SQL  Server\13 0\SDK\Asse mblies\Mic rosoft.Sql Server.Smo .dll'
  77       $SQLSv r = New-Ob ject -Type Name  Micr osoft.SQLS erver.Mana gement.Smo .Server($S sisServer)  
  78        
  79       # Chec k if job a lready exi sts. Then  fail, rena me or drop
  80       $SQLJo b = $SQLSv r.JobServe r.Jobs[$Jo bName]
  81       if ($S QLJob){
  82         # Us e one of t hese 3 opt ions to ha ndle exist ing jobs
  83        
  84         # Fa il:
  85         #Thr ow [System .Exception ] "Job wit h name '$J obName' al ready exis ts."
  86        
  87         # Re name:
  88         writ e-log -mes sage "Job  with name  '$JobName'  found, re naming and  disabling  it" -cons oleForegro und White
  89         $SQL Job.Rename ($SQLJob.N ame +"_OLD _" + (Get- Date -f MM -dd-yyyy_H H_mm_ss))
  90         $SQL Job.IsEnab led = $fal se
  91         $SQL Job.Alter( )
  92        
  93         # Dr op:
  94         #wri te-log -me ssage "Job  with name  $JobName  found, rem oving it"  -consoleFo reground Y ellow
  95         #$SQ LJob.Drop( )
  96       }
  97        
  98        
  99       #Creat e new (emp ty) job 
  100       $SQLJo b = New-Ob ject -Type Name Micro soft.SqlSe rver.Manag ement.SMO. Agent.Job  -argumentl ist $SQLSv r.JobServe r, $JobNam e
  101       $SQLJo b.OwnerLog inName = " SA"
  102       $SQLJo b.Create()  
  103       write- log -messa ge "Job '$ JobName' c reated" -c onsoleFore ground Gre en
  104        
  105        
  106       # Comm and of job step
  107       # This  string is  copied fr om T-SQL,  by scripti ng a job(s tep) in SS MS
  108       # Then  replace t he hardcod e strings  with [NAME ] to repla ce them wi th variabl es
  109       $Comma nd = @'
  110       /ISSER VER "\"\SS ISDB\[FOLD ER]\[PROJE CT]\[PACKA GE]\"" /SE RVER [SSIS SERVER] /P ar "\"$Pro ject::Conf igDB_Conne ctionStrin g\"";"\"Da ta Source= [DBSERVER] ;Initial C atalog=[CN TDB];Provi der=SQLNCL I11.1;Inte grated Sec urity=SSPI ;Auto Tran slate=Fals e;\"" /Par  "\"$Proje ct::RAMS_C onnectionS tring\"";" \"Data Sou rce=[DBSER VER];Initi al Catalog =RAMS;Prov ider=SQLNC LI11.1;Int egrated Se curity=SSP I;Auto Tra nslate=Fal se;\"" /Pa r "\"$Serv erOption:: LOGGING_LE VEL(Int16) \"";1 /Par  "\"$Serve rOption::S YNCHRONIZE D(Boolean) \"";True / CALLERINFO  SQLAGENT  /REPORTING  E
  111   '@
  112       
  113       #                      FOLDE R     PROJ ECT   PACK AGE               SSI S SERVER                                                                       DB SERVE R                   S P CNT DB                                                                                                                                            DB SE RVER                   RAMS DB                                                                                                                                                                                                                                 
  114       #                      |---- ---| |---- ---| |---- ---|             |--- -------|                                                                      |-------- |                  |- ----|                                                                                                                                               |---- ----|                   |--|                                                                                                                                                                                                                                    
  115       #/ISSE RVER "\"\S SISDB\[FOL DER]\[PROJ ECT]\[PACK AGE]\"" /S ERVER [SSI SSERVER] / Par "\"$Pr oject::Con figDB_Conn ectionStri ng\"";"\"D ata Source =[DBSERVER ];Initial  Catalog=[C NTDB];Prov ider=SQLNC LI11.1;Int egrated Se curity=SSP I;Auto Tra nslate=Fal se;\"" /Pa r "\"$Proj ect::RAMS_ Connection String\""; "\"Data So urce=[DBSE RVER];Init ial Catalo g=RAMS;Pro vider=SQLN CLI11.1;In tegrated S ecurity=SS PI;Auto Tr anslate=Fa lse;\"" /P ar "\"$Ser verOption: :LOGGING_L EVEL(Int16 )\"";1 /Pa r "\"$Serv erOption:: SYNCHRONIZ ED(Boolean )\"";True  /CALLERINF O SQLAGENT  /REPORTIN G E
  116       #/ISSE RVER "\"\S SISDB\[FOL DER]\[PROJ ECT]\[PACK AGE]\"" /S ERVER [SSI SSERVER] / Par "\"$Pr oject::Con figDB_Conn ectionStri ng\"";"\"D ata Source =[DBSERVER ];Initial  Catalog=[C NTDB];Prov ider=SQLNC LI11.1;Int egrated Se curity=SSP I;Auto Tra nslate=Fal se;\"" /Pa r "\"$Proj ect::RAMS_ Connection String\""; "\"Data So urce=[DBSE RVER];Init ial Catalo g=RAMS;Pro vider=SQLN CLI11.1;In tegrated S ecurity=SS PI;Auto Tr anslate=Fa lse;\"" /P ar "\"$Ser verOption: :LOGGING_L EVEL(Int16 )\"";1 /Pa r "\"$Serv erOption:: SYNCHRONIZ ED(Boolean )\"";True  /CALLERINF O SQLAGENT  /REPORTIN G E
  117         #/ISSERVER  "\"\SSISD B\RAMS\Ema ilNotifica tion\Train ingExpirat ionNotific ation.dtsx \"" /SERVE DN S
     /Par "\"$P roject::Co nfigDB_Con nectionStr ing\"";"\" Data Sourc e= DN S
;Initial C atalog=WSS _Content;P rovider=SQ LNCLI11.1; Integrated  Security= SSPI;Auto  Translate= False;\""  /Par "\"$P roject::RA MS_Connect ionString\ "";"\"Data  Source= DN S
;Initial C atalog=RAM S;Provider =SQLNCLI11 .1;Integra ted Securi ty=SSPI;Au to Transla te=False;\ "" /Par "\ "$ServerOp tion::LOGG ING_LEVEL( Int16)\""; 1 /Par "\" $ServerOpt ion::SYNCH RONIZED(Bo olean)\""; True /CALL ERINFO SQL AGENT /REP ORTING E
  118         #/ISSERVER  "\"\SSISD B\RAMS\Ema ilNotifica tion\Annua lReviewNot ification. dtsx\"" /S ERVER  DN S
    /Par "\"$P roject::Co nfigDB_Con nectionStr ing\"";"\" Data Sourc e= DN S
;Initial C atalog=SP_ RAMS_WSS_C ontent_IRB _Applicati on_DB_2_1; Provider=S QLNCLI11.1 ;Integrate d Security =SSPI;Auto  Translate =False;\""  /Par "\"$ Project::R AMS_Connec tionString \"";"\"Dat a Source= DN S
;Initial C atalog=RAM S;Provider =SQLNCLI11 .1;Integra ted Securi ty=SSPI;Au to Transla te=False;\ "" /Par "\ "$ServerOp tion::LOGG ING_LEVEL( Int16)\""; 1 /Par "\" $ServerOpt ion::SYNCH RONIZED(Bo olean)\""; True /CALL ERINFO SQL AGENT /REP ORTING E
  119       $Comma nd = $Comm and.Replac e("[FOLDER ]", $repor tFolder)
  120       $Comma nd = $Comm and.Replac e("[PROJEC T]", $Proj ectName)
  121       $Comma nd = $Comm and.Replac e("[PACKAG E]", $dtsx Package)
  122       $Comma nd = $Comm and.Replac e("[SSISSE RVER]", $S sisServer)
  123       $Comma nd = $Comm and.Replac e("[DBSERV ER]", $dbS erver)
  124       $Comma nd = $Comm and.Replac e("[CNTDB] ", $conten tDBName)
  125       $Comma nd = $Comm and.Replac e("[RAMSDB ]", $ramsD BName)
  126       $Comma nd = $Comm and.Replac e("[DBSERV ER]", $Ssi sServer)
  127        
  128        
  129       # Crea te new SSI S job step  with comm and from p revious bl ock 
  130       $SQLJo bStep = Ne w-Object - TypeName M icrosoft.S qlServer.M anagement. SMO.Agent. JobStep -a rgumentlis t $SQLJob,  "$Project Name - $dt sxPackage"
  131       $SQLJo bStep.OnSu ccessActio n = [Micro soft.SqlSe rver.Manag ement.Smo. Agent.Step Completion Action]::Q uitWithSuc cess
  132       $SQLJo bStep.OnFa ilAction =  [Microsof t.SqlServe r.Manageme nt.Smo.Age nt.StepCom pletionAct ion]::Quit WithFailur e
  133       $SQLJo bStep.SubS ystem = "S SIS"
  134       $SQLJo bStep.Data baseName =  $SsisServ er
  135       $SQLJo bStep.Comm and = $Com mand
  136       $SQLJo bStep.Crea te() 
  137       write- log -messa ge "Jobste p $SQLJobS tep create d" -consol eForegroun d Green
  138        
  139        
  140       # Crea te a daily  schedule
  141       $SQLJo bSchedule  = New-Obje ct -TypeNa me Microso ft.SqlServ er.Managem ent.Smo.Ag ent.JobSch edule -Arg umentList  $SQLJob, " Daily $Job StartTime"
  142       $SQLJo bSchedule. IsEnabled  = $true
  143       $SQLJo bSchedule. FrequencyT ypes = [Mi crosoft.Sq lServer.Ma nagement.S MO.Agent.F requencyTy pes]::Dail y
  144       $SQLJo bSchedule. FrequencyI nterval =  1 # Recurs  Every Day
  145       $SQLJo bSchedule. ActiveStar tDate = Ge t-Date
  146       $SQLJo bSchedule. ActiveStar tTimeofDay  = $JobSta rtTime
  147       $SQLJo bSchedule. Create()
  148       write- log -messa ge "Jobsch edule $SQL JobSchedul e created"  -consoleF oreground  Green
  149        
  150        
  151       # Appl y to targe t server w hich can o nly be don e after th e job is c reated
  152       $SQLJo b.ApplyToT argetServe r("(local) ")
  153       $SQLJo b.Alter()
  154       write- log -messa ge "$($Job Name) job  saved" -co nsoleForeg round Gree n
  155   }