Skip to content

Instantly share code, notes, and snippets.

View kulmam92's full-sized avatar

Yong Ji kulmam92

15 followers · 2 following
  • AWS
View GitHub Profile
@kulmam92
kulmam92 / Install-ISProject.ps1
Created July 6, 2013 13:48
Deploys an ispsc file to a SSISDB using MOM.
#######################
<#
.SYNOPSIS
Installs an SSIS project to a SSISDB.
.DESCRIPTION
I created this by modifying demo script shared by Bill Fellows through PASS session "Understanding the SSIS 2012 Deployment Model [LT-101]"
The Install-ISProject script installs an ispsc file to a SSISDB using MOM.
Works for 2012 and higher
.EXAMPLE
./Install-ISProject.ps1 -IspacFullName "D:\App_temp\SSIS\Test2\bin\Development\Test2.ispac" -ServerInstance "SpeakSQL\JY2012" -CatalogFolderName "DEV" -ISProjectName "test2"
@kulmam92
kulmam92 / create_execution_new.sql
Created July 6, 2013 14:03
Modified version of catalog.create_execution SP in SSISDB http://speaksql.wordpress.com/2013/06/27/ssis-2012-fail-to-start-execution-timed-out/
USE [SSISDB]
GO
/**
.SYNOPSIS
Modified version of catalog.create_execution SP.
.DESCRIPTION
I created new version of catalog.create_execution SP to reslove the execution timed out error.
Permission check logic is not included. However, everything else is the same.
Works for 2012 and higher
.EXAMPLE
@kulmam92
kulmam92 / SSIS.MSBuild.proj
Created September 4, 2013 06:19
MSBuild build script to build SSIS project using Microsoft.SqlServer.IntegrationServices.Build.dll I copied this from the below link and modified a little bit to make it work in my environment. msbuild script for building and deploying a SSIS .ispac file(http://pastebin.com/kbj8tRUz)
<?xml version="1.0" encoding="Windows-1252"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild;SSISDeploy">
<!--Requires a property called $(SSISProj) to be defined when this script is called-->
<UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
<Target Name="SSISBuild" Condition="'$(SSISProj)' != ''">
<PropertyGroup>
<SSISProjPath>.\$(SSISProj)\$(SSISProj).dtproj</SSISProjPath>
</PropertyGroup>
<Message Text="**************Building SSIS project: $(SSISProjPath) for configuration: $(CONFIGURATION)**************" />
<DeploymentFileCompilerTask InputProject="$(SSISProjPath)" Configuration="$(CONFIGURATION)" ProtectionLevel="DontSaveSensitive">
@kulmam92
kulmam92 / Deploy-SSRSProject.ps1
Last active May 16, 2017 22:48 — forked from jstangroome/Deploy-SSRSProject.ps1
Deploy SSRS 2012 project to a Reporting server using ReportService2010 management endpoint(ReportService2010.asmx?WSDL)
#requires -version 2.0
<#
.SYNOPSIS
Deploy SSRS project to a Reporting server.
.DESCRIPTION
The Deploy-SSRSProject script installs an SSRS project(DataSource, DataSet, Report) file to a Reporting Server using ReportService20XX management endpoint.
http://technet.microsoft.com/en-us/library/ee640743(v=sql.105).aspx
1. Get datasource, report from the .rptproj file
2. Get report list from the $ReportListFile file
@kulmam92
kulmam92 / Defalut.nuspec
Last active April 24, 2021 17:39 — forked from PaulStovell/gist:2944609
Create Nuget package for SSIS using MSBuild
<?xml version="1.0"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
<metadata>
<id>_Dummy</id>
<version>1.0.0</version>
<authors>DBA</authors>
<owners>SpeakSQL</owners>
<licenseUrl>http://a.c</licenseUrl>
<projectUrl>http://a.c</projectUrl>
<requireLicenseAcceptance>false</requireLicenseAcceptance>
@kulmam92
kulmam92 / DB.Schema.SVNCheckin.bat
Created September 23, 2013 13:35
Check in DB schema to SVN using SQLCompare.
SVN add --username MyUser --password MyUserP@ss --force * --auto-props --parents --depth infinity
SVN commit --username MyUser --password MyUserP@ss -m "Daily Checkin"
@kulmam92
kulmam92 / EmailLastFailedStep_SendErrorMessage.sql
Last active August 29, 2015 14:03
How to get detailed error message of last step when job failed.
USE [msdb]
GO
CREATE PROC [dbo].[EmailLastFailedStep_SendErrorMessage]
@JobName nvarchar(128)
,@recipients varchar(4000)
,@profile_name nvarchar(128)
,@debug bit = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
@kulmam92
kulmam92 / Job_FailaureMonitoring.sql
Created January 20, 2015 23:04
CREATE PROC [dbo].[Job_FailaureMonitoring]
@HourInterval int = 24
,@recipients varchar(4000)
,@profile_name nvarchar(128)
,@debug bit = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
declare @body varchar(max)
@kulmam92
kulmam92 / Install-ISPackage.ps1
Created April 8, 2015 01:20
Installs an SSIS package to a SQL Server store - archiving from http://sev17.com/2012/11/06/scripting-ssis-package-deployments/
#######################
<#
.SYNOPSIS
Installs an SSIS package to a SQL Server store.
.DESCRIPTION
The Install-ISPackage script installs an Dtsx file to a SQL Server store using the command-line utility dtutil.
Works for 2005 and higher
.EXAMPLE
./install-ispackage.ps1 -DtsxFullName "C:\Users\Public\bin\SSIS\sqlpsx1.dtsx" -ServerInstance "Z001\SQL1" -PackageFullName "SQLPSX\sqlpsx1"
This command install the sqlpsx1.dtsx package to SQL Server instance Z001\SQL1 under the SQLPSX folder as sqlpsx1. If SQLPSX folder does not exist it will be created.
@kulmam92
kulmam92 / gist:aac56833c0c707b7a928
Last active August 29, 2015 14:19 — forked from mwrock/gist:8622431
#install SqlServer and TFS
cinst VisualStudioTeamFoundationServerExpress2013
cinst MsSqlServer2012ExpressWithReporting
#Configure SQL Reporting Services
$rsConfig = (Get-WmiObject `
-namespace "root\Microsoft\SqlServer\ReportServer\RS_MSSQLSERVER\v11\admin" `
-class "MSReportServer_ConfigurationSetting")
#Configuring Reporting Server Virtual Directories