<#@ template language="C#" #>
<#@ assembly name="C:\\Windows\\Microsoft.NET\\Framework\\v2.0.50727\\System.Windows.Forms.dll" #>
<#@ import namespace="System.Windows.Forms" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.OleDb" #>
<#@ import namespace="System.Text" #>
  
<#
    /* ========================================================================= */
    /* ===                GENERATE STAGING LOADING PACKAGE                   === */
    /* ========================================================================= */
    /*      Version:        1                                                    */
    /*      Release Date:   2013-07-23                                           */
    /*      Authors:        Luca Zavarella (SolidQ.com)                          */
    /*      License:        Creative Commons Attribution-ShareAlike 3.0          */
    /*                      Italy License                                        */
    /*         (http://creativecommons.org/licenses/by-sa/3.0/it/deed.en)        */
    /* ===-------------------------------------------------------------------=== */
    /* ===                            DESCRIPTION                            === */
    /* ===-------------------------------------------------------------------=== */
    /*      This Biml script will generate a SSIS package that will create,      */
    /*      or simply use, a staging table from a given staging view and will    */
    /*      persist data from the view to the table. This is a typical task in   */
    /*      the Staging Phase of a BI Project.                                   */
    /*      Dropping and creation of an existent destination table is allowed.   */
    /*      In this case, you will be prompted to continue in the operation.     */
    /*      If needed, indexes dropping and creation is allowed using stored     */
    /*      procedures.                                                          */
    /*      The Adaptive BI Framework (*) for Data Warehousing by Davide Mauri   */
    /*      is supported.                                                        */
    /*      SQL Server 2008 (R2) and SQL Server 2012 are supported.              */
    /* ===-------------------------------------------------------------------=== */
    /* ===                              WARNINGS                             === */
    /* ===-------------------------------------------------------------------=== */
    /*      Always check that in the OLE DB Destination all the fields are       */
    /*      mapped in the right way. If a field name in the source view          */
    /*      differs from the destination name of the corresponding field in      */
    /*      the table, a manual mapping is needed.                               */
    /* ========================================================================= */
      
    // (*) http://speakerrate.com/talks/4577-adaptive-bi-engineering-a-bi-solution
      
      
      
    /* ========================================================================= */
    /* ===                        FRAMEWORK SETTINGS                         === */
    /* ========================================================================= */
      
    //
    // SSIS Package details:
    //    The SSIS package name will be like:
    //       <packagePrefix><packageOrder><tableName>
    //
    //    E.g. "Prepare 999 my_table.dtsx"
    string packagePrefix =  "Prepare ";
    string packageOrder =   "999 ";
	string srcConnectionManagerName = "STG";
	string dstConnectionManagerName = "TMP";
      
    //
    // Staging Database details:
    //    The source view that MUST exist in the Staging database is like:
    //       <viewSchemaName><viewPrefix><tableName>
    //
    //       E.g. "[etl].[vw_my_table]"
    string viewSchemaName =             "etl";
    string viewPrefix =                 "vw_";
    string tableSchemaName =            "tmp";
    string storedProcedureSchemaName =  "etl";
      
      
    /* ========================================================================= */
    /* ===                          INPUT PARAMETERS                         === */
    /* ========================================================================= */
      
    //
    // General settings:
    //   - dropAndCreateTable:      if 'true', the script'll create (and drop if exists) a new table
    //                              with the same schema of the source view; if 'false' the table MUST exists;
    //   - dropAndCreateIndex:      if 'true', the generated SSIS package will contain two SQL Tasks to drop
    //                              and create indexes (through stored procedures)
    bool dropAndCreateTable = false;
    bool dropAndCreateIndex = false;
      
      
    //
    // SQL Server server name/instance and Staging database name
    //
    string stgServerName =      "localhost";
    string stgDatabaseName =    "MyProjectSTG";
      
      
    //
    // Database objects settings:
    //   - tableName:                       name of the destination table (without the schema);
    //   - dropIndexStoredProcedureName:    name of the stored procedure containing the script to drop indexes;
    //   - createIndexStoredProcedureName:  name of the stored procedure containing the script to create indexes;
    string tableName =                      "my_table";
    string dropIndexStoredProcedureName =   "stp_drop_index_tmp_my_table";
    string createIndexStoredProcedureName = "stp_create_index_tmp_my_table";
      
      
    /*
        The stored procedure I often use to drop and create indexes are like these ones:
          
        CREATE PROCEDURE [etl].[stp_create_index_...] AS
        CREATE NONCLUSTERED INDEX [IX_NC_...] ON [tmp].[my_table] 
        (
            <fields>
        )
      
      
        CREATE PROCEDURE [etl].[stp_drop_index_...]
        AS 
        IF EXISTS ( SELECT
                        *
                    FROM
                        sys.indexes
                    WHERE
                        object_id = OBJECT_ID(N'[tmp].[my_table]')
                        AND name = N'IX_NC_...' ) 
            DROP INDEX [IX_NC_...] ON [tmp].[my_table]
    */
      
      
    /* ======================================================================= */
    /* ===     DO NOT DELETE/MODIFY ANYTHING UNDER THIS PLACEHOLDER!!      === */
    /* ======================================================================= */
      
    string connSTG = String.Format("Data Source={0};Initial Catalog={1};Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-{2};",
                                    stgServerName, stgDatabaseName, srcConnectionManagerName);
                                      
    string connTMP = String.Format("Data Source={0};Initial Catalog={1};Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-{2};",
                                    stgServerName, stgDatabaseName, dstConnectionManagerName);
      
    string packageFullName = packagePrefix + packageOrder + tableName;
    string tableFullName = tableSchemaName + "." + tableName;
    string viewFullName = viewSchemaName + "." + viewPrefix + tableName;
    string columnName = "";
    string columnsString = "";
    string tableDefinitionColumn = "";
    string tableDefinitionInit = String.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'U')){2}" +
                                     "DROP TABLE [{0}].[{1}];{2}CREATE TABLE [{0}].[{1}](", tableSchemaName, tableName, Environment.NewLine);
    string tableDefinitionString = "";
  
  
    DataTable t = ExternalDataAccess.GetDataTable(connTMP,
                    String.Format("SELECT t1.COLUMN_NAME, " +
                                  "TABLE_DEFINITION_COLUMN = " +
                                  "'[' + t1.COLUMN_NAME + '] ' + " +
                                  "CASE " +
                                        "WHEN t1.DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar', 'binary') " +
                                            "THEN UPPER(t1.DATA_TYPE) + '(' + CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ') ' " +
                                        "WHEN t1.DATA_TYPE IN ('decimal', 'numeric') " +
                                            "THEN UPPER(t1.DATA_TYPE) + '(' + CAST(t1.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(t1.NUMERIC_SCALE AS VARCHAR(10)) + ') ' " +
                                        "ELSE UPPER(t1.DATA_TYPE) + ' ' " +
                                    "END + " +
                                    "CASE " +
                                        "WHEN t1.IS_NULLABLE = 'NO' " +
                                        "THEN 'NOT ' ELSE '' " +
                                    "END + 'NULL,' " +
                                  "FROM INFORMATION_SCHEMA.COLUMNS AS t1 " +
                                  "WHERE t1.TABLE_CATALOG = '{0}' " +
                                  "AND t1.TABLE_NAME = '{1}{2}' " +
                                  "ORDER BY t1.ORDINAL_POSITION",
                            stgDatabaseName,
                            viewPrefix,
                            tableName)
                    );
  
    StringBuilder sb = new StringBuilder();
    StringBuilder tableDefinition = new StringBuilder(tableDefinitionInit);
      
    foreach (DataRow dr in t.Rows)
    {
        columnName = dr[0].ToString();
        tableDefinitionColumn = dr[1].ToString();
          
        sb.Append(",   [" + columnName + "]" + Environment.NewLine);
        tableDefinition.Append(String.Format("{0}{1}", tableDefinitionColumn, Environment.NewLine));
    }
    columnsString = Environment.NewLine + new String(' ', 3) + sb.ToString().Substring(3, sb.Length - 4);
    tableDefinitionString = tableDefinition.ToString().Substring(0, tableDefinition.Length - 3) + ");";
      
  
    DialogResult dialogResult = default(DialogResult);
                     
    if (dropAndCreateTable)
    {
        dialogResult = MessageBox.Show(
                            String.Format("If exists the table '[{0}].[{1}]' will be dropped and re-created. Do you want to continue?",
                                          tableSchemaName, tableName),
                            "WARNING", MessageBoxButtons.YesNo);
          
        if (dialogResult == DialogResult.Yes)
            using (OleDbConnection connection = new OleDbConnection(connTMP))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand(tableDefinitionString, connection);
                command.ExecuteNonQuery();
            }
    }
      
    if (
        (dropAndCreateTable && dialogResult == DialogResult.Yes)
        || !dropAndCreateTable)
    {
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="<#=dstConnectionManagerName#>" ConnectionString="<#=connTMP#>" />
        <OleDbConnection Name="<#=srcConnectionManagerName#>" ConnectionString="<#=connSTG#>" />
    </Connections>
    <Packages>
        <Package Name="<#=packageFullName#>" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey">
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="Warning">Check Data Flow Destination mappings!</Annotation>
            </Annotations>
            <Tasks>
                <ExecuteSQL Name="Clean TMP Table" ConnectionName="<#=dstConnectionManagerName#>">
                    <DirectInput>TRUNCATE TABLE <#=tableFullName#>;</DirectInput>
                </ExecuteSQL>
                  
                <# if (dropAndCreateIndex) { #>
                    <!-- Drop index -->
                    <ExecuteSQL Name="Drop Index" ConnectionName="<#=dstConnectionManagerName#>">
                        <Annotations>
                            <Annotation AnnotationType="Description">Execute SQL Task</Annotation>
                        </Annotations>
                        <DirectInput>EXEC <#=storedProcedureSchemaName#>.<#=dropIndexStoredProcedureName#>;</DirectInput>
                    </ExecuteSQL>
                <# } #>
                <Dataflow Name="Load Staging Table">
                    <Transformations>
                        <OleDbSource Name="<#=srcConnectionManagerName#>" ConnectionName="<#=srcConnectionManagerName#>">
                            <DirectInput><![CDATA[SELECT <#=columnsString#>FROM <#=viewFullName#><#=Environment.NewLine#>OPTION (RECOMPILE);]]></DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="<#=dstConnectionManagerName#>" ConnectionName="<#=dstConnectionManagerName#>" UseFastLoadIfAvailable="true">
                            <InputPath OutputPathName="<#=srcConnectionManagerName#>.Output" />
                            <ExternalTableOutput Table="<#=tableFullName#>" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
                <# if (dropAndCreateIndex) { #>
                    <!-- Create index -->
                    <ExecuteSQL Name="Create Index" ConnectionName="<#=dstConnectionManagerName#>">
                        <Annotations>
                            <Annotation AnnotationType="Description">Execute SQL Task</Annotation>
                        </Annotations>
                        <DirectInput>EXEC <#=storedProcedureSchemaName#>.<#=createIndexStoredProcedureName#>;</DirectInput>
                    </ExecuteSQL>
                <# } #>
            </Tasks>
        </Package>
    </Packages>
</Biml>
    <# } // ...if drop&create table #>