kmoormann’s gists

kmoormann / ClearDir.ps1

Created August 15, 2012 16:35

PowerShell delete all items in directory and all subdirectories

	function Global:ClearDir([string] $PATH)
	{
	$AllItemsInPath = $Path + "*"
	Remove-Item $AllItemsInPath -recurse -force
	}

kmoormann / CopyFilesInFolder.ps1

Created August 20, 2012 19:17

CopyFilesByExtension

	function Global:CopyFilesInFolder([string] $FromDir, [string] $ToDir, [string] $FileExtension)
	{
	#Destination for files
	$From = $FromDir + "*" + $FileExtension
	Copy-Item $From $ToDir
	}

kmoormann / gist:3504218

Last active October 9, 2015 12:17

ssis time string concatenation

	(DT_WSTR, 4) YEAR( @[System::StartTime] )
	+ "-"
	+ (DT_WSTR, 1)( MONTH( @[System::StartTime] ) < 10 ? (DT_WSTR, 1)"0" : (DT_WSTR, 0)"" )
	+ (DT_WSTR, 4) MONTH ( @[System::StartTime] )
	+ "-"
	+ (DT_WSTR, 1)( DAY( @[System::StartTime] ) < 10 ? (DT_WSTR, 1)"0" : (DT_WSTR, 0)"" )
	+ (DT_WSTR, 4) DAY ( @[System::StartTime] )
	+ " "
	+ (DT_WSTR, 1)( DATEPART( "Hour" , @[System::StartTime] ) < 10 ? (DT_WSTR, 1)"0" : (DT_WSTR, 0)"" )
	+ (DT_WSTR, 2) DATEPART( "Hour" , @[System::StartTime] )

kmoormann / AccessSSISVariableInScriptTask.cs

Created August 31, 2012 17:54

How to access SSIS variables in Script Task

	//note that variable name spaces are not to be used and variables are case sensitive
	object myVar = Dts.Variables["MyCaseSensitiveVariableName"].Value;

kmoormann / IterateResultSetSSIS.cs

Created August 31, 2012 18:58

Iterate over a result set in SSIS

	//be sure to reference System.Xml;
	//need a using using System.Data.OleDb;

	//set up an adapter and a data table to iterate over
	var resultsAdapter = new OleDbDataAdapter();
	var resultsTable = new DataTable();
	resultsAdapter.Fill(resultsTable, Dts.Variables["ZipCodesResultSet"].Value);
	var list = new StringBuilder();

	int count = 0;

kmoormann / MVCEnumHelper.cs

Created September 3, 2012 04:43

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Linq.Expressions;
	using System.Web;
	using System.Web.Mvc;
	using System.Web.Mvc.Html;

	namespace MyProject.Helpers
	{

kmoormann / SimpleSchema.sql

Created September 5, 2012 01:45

Simple Schema Output for MSSQL

	SELECT
	s.name [Schema Name],
	tables.name [Table Name],
	c.name [Column Name],
	ISNULL(i.is_primary_key, 0) [Is Primary Key],
	t.Name [Data type],
	c.max_length [Max Length],
	c.precision ,
	c.scale ,
	c.is_nullable,

kmoormann / InsertScriptFromSSISConfiguration.sql

Created September 10, 2012 23:25

INSERT INTO FOR SSIS Configurations

	SELECT
	'INSERT INTO [dbo.SSISConfig] '
	+CHAR(10)+CHAR(13)+CHAR(9) + '([ConfigurationFilter]'
	+CHAR(13)+CHAR(10)+CHAR(9) + ',[ConfiguredValue]'
	+CHAR(13)+CHAR(10)+CHAR(9) + ',[PackagePath]'
	+CHAR(13)+CHAR(10)+CHAR(9) + ',[ConfiguredValueType])'
	+CHAR(13)+CHAR(10)+CHAR(9) + 'VALUES'
	+CHAR(13)+CHAR(10)+CHAR(9) + '(N''' + ConfigurationFilter + ''''
	+CHAR(13)+CHAR(10)+CHAR(9) + ',N'''+ConfiguredValue+''''
	+CHAR(13)+CHAR(10)+CHAR(9) + ',N'''+ConfiguredValueType+''''

kmoormann / IdempotentSQLAlterTableAddColumn.sql

Created September 20, 2012 20:47

Idempotent SQL Alter Table Statements

	IF NOT EXISTS
	(
	SELECT * FROM [information_schema].[columns]
	WHERE table_name = 'Customer'
	AND table_schema = 'dbo'
	AND column_name = 'FavoriteColorId'
	)
	BEGIN
	ALTER TABLE [dbo].[Customer]
	ADD FavoriteColorId int

kmoormann / SQLAgentStart.sql

Created September 26, 2012 14:46

SQL Server Agent Starting and Stopping

	--######## FOR STARTING WHEN STOPPED OR STOPPING ####################
	--IF YOU NEED TO STOP THE SERVER FIRST TO TRUN THE LINE BELOW
	--EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT';
	USE master;
	GO

	CREATE TABLE #SQLAgentStatus
	(
	Status varchar(50),
	Timestamp datetime default (getdate())

Kevin Moormann kmoormann