mlongoria

<#* 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

<#*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

--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

  {
        "name": "Date",
        "dataCategory": "Time",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Date",
            "dataType": "dateTime",
            "isNameInferred": true,
            "isDataTypeInferred": true,

mlongoria

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "MyTabularModel"
      }
    ]
  }
}

mlongoria

<#@ 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

<#@ 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

<#@ 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

<!-- 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

CREATE PROC SwapWithMetadata
    @SrcSchema NVARCHAR(128),
    @SrcTable NVARCHAR(128),
    @DestSchema NVARCHAR(128),
    @DestTable NVARCHAR(128),
    @TransferMetadata BIT,
    @DropOldTable BIT
AS

BEGIN