Skip to content

Instantly share code, notes, and snippets.

View matt40k's full-sized avatar

Matt Smith matt40k

31 followers · 9 following
View GitHub Profile
@matt40k
matt40k / Deploy-SSRS.ps1
Last active January 21, 2016 13:45
Deploys SSRS reports from NuGet package via @OctopusDeploy by @twerthi - https://github.com/OctopusDeploy/Library/blob/master/step-templates/ssrs-deploy-from-package.json
$DeployedPath = $OctopusParameters["Octopus.Action[$NugetPackageStepName].Output.Package.InstallationDirectoryPath"]
$ReleaseNumber = $OctopusParameters["Octopus.Release.Number"]
#region Upload-Item
Function Upload-Item
{
# parameters
param ([string] $Item, [string]$ItemType, [string] $ItemFolder)
@matt40k
matt40k / BooleanExample.csv
Created January 6, 2016 22:54
Sims Bulk Import - Example of a Boolean import file for Waters Edge
Admission Number Single Parent Family
001103 true
001104 false
001275 1
001336 0
@matt40k
matt40k / LogFile.txt
Created January 6, 2016 22:53
SIMS Bulk Import - 2.3.33.0 - failed to import Boolean UDF
2016-01-06 22:39:11.3040|Trace|Trace:: Matt40k.SIMSBulkImport.Program.Main()
2016-01-06 22:39:12.0384|Trace|Trace:: Matt40k.SIMSBulkImport.Login()
2016-01-06 22:39:12.2102|Info|==============================================================================================
2016-01-06 22:39:12.2259|Info|==============================================================================================
2016-01-06 22:39:12.2259|Info|
2016-01-06 22:39:12.2259|Info|SIMS Bulk Import - 2.3.33.0
2016-01-06 22:39:12.2415|Info|
2016-01-06 22:39:12.2415|Debug|Clearing up
2016-01-06 22:39:12.2415|Trace|Trace:: Matt40k.SIMSBulkImport.ClearTmp()
2016-01-06 22:39:12.2571|Trace|Trace:: Matt40k.SIMSBulkImport.Prefix.GetPrefix(GET)
@matt40k
matt40k / SSISCatalog.sql
Created January 4, 2016 13:17
Ripped from Jamie Thomson - http://sqlblog.com/blogs/jamie_thomson/archive/2012/10/17/querying-the-ssis-catalog-here-s-a-handy-query.aspx
SELECT event_message_id,MESSAGE,package_name,event_name,message_source_name,package_path,execution_path,message_type,message_source_type
FROM (
SELECT em.*
FROM SSISDB.catalog.event_messages em
WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
AND event_name NOT LIKE '%Validate%'
)q
/* Put in whatever WHERE predicates you might like*/
--WHERE event_name = 'OnError'
--WHERE package_name = 'Package.dtsx'
@matt40k
matt40k / CreateJobSchedule.sql
Created January 4, 2016 12:31
Create schedules for SQL job
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_id=N'd938409a-2ffb-4b7c-8bd6-8cf3dc557300', @name=N'One-Time',
@enabled=1,
@freq_type=1,
@freq_interval=1,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@matt40k
matt40k / pupil.csv
Last active January 4, 2016 11:38
Example of a Pupil CSV file for SIMS Bulk Import
Forename Surname Year EmailAddress
Bill Gates Year10 [email protected]
Satya Nadella Year7 [email protected]
Steve Ballmer Year8 [email protected]
@matt40k
matt40k / AddMissingDescriptionOnAuditColumns.ps1
Created December 22, 2015 15:58
Takes the list of tables with missing audit column descriptions then adds it on
$file = "C:\temp\audit\staging.csv"
$csv = Import-Csv $file
$ssdtDir = 'C:\GIT\BusinessIntelligence\BusinessIntelligence\1_SQL_Staging\SRC\'
ForEach ($line in $csv) {
$sqlFilePath = $ssdtDir + $line.SchemaName + '\Tables\' + $line.TableName + '.sql'
Write-Host $sqlFilePath
@matt40k
matt40k / ListMissingDescriptionOnAuditColumns.sql
Created December 22, 2015 14:53
Lists all the tables that are missing descriptions on the audit columns
select
SchemaName = schema_name(t.schema_id)
,TableName = t.name
from
sys.tables t
left outer join (
select
SchemaName
,TableName
from
@matt40k
matt40k / UpdateVarName.ps1
Last active December 22, 2015 11:36
Update SSIS package with a new var name or such
$dtsFiles = Get-ChildItem "C:\GIT\BusinessIntelligence\" -Filter *.dts
Foreach($dtsFile in $dtsFiles)
{
Write-Host $dtsFile.FullName
$dtsContents = Get-Content $dtsFile.FullName
$dtsContents = $dtsContents -replace "audit.USP_ErrorLog", "[Audit].[USP_ErrorLog]"
$dtsContents = $dtsContents -replace "audit.USP_JobLog", "[Audit].[USP_JobLog]"
$dtsContents = $dtsContents -replace "audit.USP_RunTableRowCounts", "[Audit].[USP_RunTableRowCounts]"
@matt40k
matt40k / 01-CreateProjectParameters.biml
Created December 22, 2015 00:04
Create SSIS project parameters using BIMl - by Cathrine Wilhelmsen
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Projects>
<PackageProject Name="ProjectParameters">
<Annotations>
<Annotation Tag="ProjectParametersPath">C:\SSIS\TestProject\Project.params</Annotation>
</Annotations>
<Parameters>
<Parameter Name="ParameterBoolean" DataType="Boolean" IsRequired="false" IsSensitive="false">0</Parameter>
<Parameter Name="ParameterDateTime" DataType="DateTime" IsRequired="false" IsSensitive="false">2015-11-04</Parameter>
<Parameter Name="ParameterDecimal" DataType="Decimal" IsRequired="false" IsSensitive="false">3.14</Parameter>