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.
# | 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 |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 4 | 302 |
Changed | 3 | 8 |
Inserted | 0 | 0 |
Removed | 0 | 0 |
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 |
No regular expressions were active.
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 e | |
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
|
|
30 | ||
31 | .EXAMP LE | |
32 | Create-SSI SAgentJob -ssisServe r " DN S " -dbServe r " DN S
|
|
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 R DN S
|
|
118 | #/ISSERVER "\"\SSISD B\RAMS\Ema ilNotifica tion\Annua lReviewNot ification. dtsx\"" /S ERVER DN S
|
|
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 | } |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.