Skip to content

Instantly share code, notes, and snippets.

View mlongoria's full-sized avatar

mlongoria

11 followers · 0 following
View GitHub Profile
@mlongoria
mlongoria / PL_Copy_MySourceDBToADLS.biml
Last active March 10, 2017 19:21
Biml ADF Pipeline Generation
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Text" #>
<#@ property name="targetTables" type="DataView"#>
<#@ property name="frequency" type="string"#>
<#@ property name="scope" type="string"#>
{
"$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Pipeline.json",
"name": "PL_Copy_MySourceDBToADLS_<#=frequency#>_<#=scope#>",
"properties": {
"description": "<#=frequency#> <#=scope#> copies of data from Source db to the data lake.",
@mlongoria
mlongoria / Biml ADF Dataset Generation Snippet.txt
Last active March 10, 2017 18:49
A file that generates Azure Data Factory datasets and pipelines via Biml
<#@ template tier="10" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Text" #>
<#@ code file="BGHelper.cs" #>
<#@ import namespace="BGHelper" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>
<#
string mdFilePath = "C:\\Users\\admin\\Source\\Workspaces\\SH Data Warehouse\\metadata";
@mlongoria
mlongoria / 1_DS_OnPremSQL_MySourceDB_Schema_Table.biml
Last active March 3, 2017 18:09
BimlScript that is used with an Excel spreadsheet to define datasets and copy pipelines in Azure Data Factory for SQL to ADLS. Needed: List of tables with schema, frequency, indicator of incremental or full load, and field used for window if incremental load
<#@ property name="schema" type="string" #>
<#@ property name="table" type="string" #>
<#@ property name="frequency" type = "string" #>
{
"$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",
"name": "DS_OnPremSQL_MySourceDB_<#=schema#>_<#=table#>",
"properties": {
"type": "SqlServerTable",
"linkedServiceName": "LS_OnPremSQL_MySourceDB",
@mlongoria
mlongoria / SSASProcessFullTMSL.xmla
Created September 19, 2016 20:38
Does a Process Full for a 2016+ Tabular SSAS model using TMSL
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyTabularModel"
}
]
}
}
@mlongoria
mlongoria / SSASTabDateDim.JSON
Last active September 2, 2016 15:43
Creates a Date dim with fiscal calendar all based off of a calculated table. Assumes months are calendar. Fiscal Year start determined by value in [Fiscal Year Month Begin]
{
"name": "Date",
"dataCategory": "Time",
"columns": [
{
"type": "calculatedTableColumn",
"name": "Date",
"dataType": "dateTime",
"isNameInferred": true,
"isDataTypeInferred": true,
@mlongoria
mlongoria / RemoveLineBreaks.sql
Created August 6, 2016 18:18
Dynamically creates a select query for all fields in a table that replaces line breaks in string fields with 4 spaces
--Change @Tablename on Line3 and run for each table
DECLARE @TableName varchar(100);
Set @TableName = 'Goods'
DECLARE @ColumnList NVARCHAR(MAX);
SET @ColumnList ='';
Declare @sqlselect nvarchar(max);
SELECT @ColumnList = @ColumnList + ', ' +
CASE WHEN data_type in ('char','varchar','nchar','nvarchar') THEN 'REPLACE(REPLACE('+ CAST(COLUMN_NAME AS VARCHAR(128)) +', CHAR(13), char(32) + char(32) + char(32) + char(32)), CHAR(10), char(32) + char(32) + char(32) + char(32))' + CAST(COLUMN_NAME AS VARCHAR(128))
WHEN data_type in ('text','ntext') THEN 'REPLACE(REPLACE(Cast([' + CAST(COLUMN_NAME AS VARCHAR(128)) + '] as varchar(8000)), CHAR(13), char(32) + char(32) + char(32) + char(32)), CHAR(10), char(32) + char(32) + char(32) + char(32)) ' + CAST(COLUMN_NAME AS VARCHAR(128))
@mlongoria
mlongoria / Dim1Caller.biml
Last active July 1, 2016 13:59
Caller file for Type 1 SCD design pattern.
<#*This file retrieves variable values from a database
and passes them to another Biml file file that contains
the design pattern for a type 1 SCD*#>
<#*The items directly below this comment are directives*#>
<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
@mlongoria
mlongoria / TextNuggetReplaceNames.biml
Last active June 30, 2016 05:15
Demonstration of using text nuggets in Biml
<#* The variables below are control nuggets.
They will be discussed later but are needed for ths example.*#>
<#
var PackageName = "Stage_AW_SalesReason";
var SourceTable = "SalesReason";
var SourceSchema = "Sales";
var DestinationTable = "SalesReason";
var DestinationSchema = "Staging";
#>