<#@ 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 #>
Generate an ETL Loading Package
lucazav
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.
published 07.23.13
last updated 07.23.13
Comments
There are no comments yet.