Skip to content

Instantly share code, notes, and snippets.

@mlongoria
mlongoria / TextNuggetReplaceNames.biml
Last active Jun 30, 2016
Demonstration of using text nuggets in Biml
View TextNuggetReplaceNames.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";
#>
@mlongoria
mlongoria / Dim1Caller.biml
Last active Jul 1, 2016
Caller file for Type 1 SCD design pattern.
View Dim1Caller.biml
<#*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 / RemoveLineBreaks.sql
Created Aug 6, 2016
Dynamically creates a select query for all fields in a table that replaces line breaks in string fields with 4 spaces
View RemoveLineBreaks.sql
--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 / SSASTabDateDim.JSON
Last active Sep 2, 2016
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]
View SSASTabDateDim.JSON
{
"name": "Date",
"dataCategory": "Time",
"columns": [
{
"type": "calculatedTableColumn",
"name": "Date",
"dataType": "dateTime",
"isNameInferred": true,
"isDataTypeInferred": true,
@mlongoria
mlongoria / SSASProcessFullTMSL.xmla
Created Sep 19, 2016
Does a Process Full for a 2016+ Tabular SSAS model using TMSL
View SSASProcessFullTMSL.xmla
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyTabularModel"
}
]
}
}
@mlongoria
mlongoria / 1_DS_OnPremSQL_MySourceDB_Schema_Table.biml
Last active Mar 3, 2017
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
View 1_DS_OnPremSQL_MySourceDB_Schema_Table.biml
<#@ 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 / Biml ADF Dataset Generation Snippet.txt
Last active Mar 10, 2017
A file that generates Azure Data Factory datasets and pipelines via Biml
View Biml ADF Dataset Generation Snippet.txt
<#@ 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 / PL_Copy_MySourceDBToADLS.biml
Last active Mar 10, 2017
Biml ADF Pipeline Generation
View PL_Copy_MySourceDBToADLS.biml
<#@ 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.",
View TF Dynamics CRM Source Biml
<!-- This goes in your data flow as the source -->
<CustomComponent Name="TF Dynamics 365 <#=table.Name#>" ComponentTypeName="PragmaticWorks.TaskFactory.DynamicsSource" Version="1" ContactInfo="Dynamics Source;Pragmatic Works, Inc; Task Factory (c) 2009 - 2016 Pragmatic Works, Inc; http://www.pragmaticworks.com;support@pragmaticworks.com" UsesDispositions="true">
<Annotations>
<Annotation AnnotationType="Description">Extract data from Microsoft Dynamics CRM.</Annotation>
</Annotations>
<DataflowOverrides>
<OutputPath OutputPathName="Dynamics Source Output">
<!-- Iterate through the columns (I'm passing in table and column from another file), ignoring any audit columns you may have added to your table. Set the Error Row Disposition and Truncation Row Disposition. -->
<# foreach (var column in table.Columns.Where(c => !c.Name.Equals("AuditETLLogID") && !c.Name.Equals("AuditIn
@mlongoria
mlongoria / SQLDWTableSwapWithClassifications
Created May 29, 2019
Stored procedure to swap tables via RENAME and DROP TABLE and transfer any sensitivity classifications from the original table to the new table
View SQLDWTableSwapWithClassifications
CREATE PROC SwapWithMetadata
@SrcSchema NVARCHAR(128),
@SrcTable NVARCHAR(128),
@DestSchema NVARCHAR(128),
@DestTable NVARCHAR(128),
@TransferMetadata BIT,
@DropOldTable BIT
AS
BEGIN