BIML script to create package in Data Mart level

Incremental Load with Connection Managers, Configuration, Variables and EventHandler

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="LSDM20" ConnectionString="Data Source=<servername>;Initial Catalog=ListSelect20_Stage;Provider=SQLNCLI11.1;OLE DB Services=1;Integrated Security=SSPI;" />
<Connection Name="EDW20" ConnectionString="Data Source=<servername>;Initial Catalog=EDW20_Stage;Provider=SQLNCLI11.1;OLE DB Services=1;Integrated Security=SSPI;" />
</Connections>
<Packages>
<Package Name="LSDM20_Template" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">
<!-- Variable Declartion-->
<Variables>
<Variable Name="AuditId" DataType="Int32" InheritFromPackageParentConfigurationString="AuditId" > -1 </Variable>
<Variable Name="AuditTaskId" DataType="Int32"> 0 </Variable>
<Variable Name="IsPackageLevel" DataType="Boolean"> false </Variable>
<Variable Name="IsParentValue" DataType="Boolean"> false </Variable>
<Variable Name="LastModDate" DataType="String"> </Variable>
<Variable Name="RowsAffected" DataType="Int32"> 0 </Variable>
<Variable Name="Status" DataType="String">Completed </Variable>
<Variable Name="TableName" DataType="String">Template_Aprimo </Variable>
<Variable Name="SchemaName" DataType="String"> dbo </Variable>
<Variable Name="EDWTableName" DataType="String">Template_Aprimo </Variable>
<Variable Name="IsInitialLoad" DataType="Int32">1 </Variable>
<Variable Name="MaxAuditTaskId" DataType="Int32">0 </Variable>
</Variables>
<!-- Package Configurations -->
<PackageConfigurations>

<PackageConfiguration Name="LSDM20_Connection">
<IndirectFileInput EnvironmentVariable="MDR_LSDM_ConnectionConfig_Path"></IndirectFileInput>
</PackageConfiguration>
<PackageConfiguration Name="EDW20_Connection">
<IndirectFileInput EnvironmentVariable="MDR_EDW_ConnectionConfig_Path"></IndirectFileInput>
</PackageConfiguration>
</PackageConfigurations>

<Events>
<Event EventType="OnError" Name="OnError" ConstraintMode="Parallel">

<Tasks>
<Script  Name="SRC setOnError" LoggingMode="Enabled" >
<ScriptTaskProject>
<ScriptTaskProject ScriptLanguage="CSharp" EntryPoint="Main" VstaMajorVersion="2" VstaMinorVersion="1" ProjectCoreName="Test" Name="Test Script">
<AssemblyReferences>
<AssemblyReference AssemblyPath="System"></AssemblyReference>
                
                <AssemblyReference AssemblyPath="System.Data"></AssemblyReference>
                <AssemblyReference AssemblyPath="System.Windows.Forms"></AssemblyReference>
                <AssemblyReference AssemblyPath="System.Xml"></AssemblyReference>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS"></AssemblyReference>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask"></AssemblyReference>

</AssemblyReferences>
<ReadWriteVariables>
<Variable VariableName="Status" DataType="String" Namespace="User"></Variable>
</ReadWriteVariables>
<Files>

<File Path="ScriptMain.cs">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

    public void Main()
    {
Dts.Variables["User::Status"].Value = "OnError";
Dts.TaskResult = (int)ScriptResults.Success;
    }
}
                </File>
                <File Path="AssemblyInfo.cs" BuildAction="Compile">using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyTitle("Test.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Microsoft")]
[assembly: AssemblyProduct("Test.csproj")]
[assembly: AssemblyCopyright("Copyright @ Microsoft 2013")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

[assembly: AssemblyVersion("1.0.*")]


</File>
</Files>
</ScriptTaskProject>
</ScriptTaskProject>
</Script>
<ExecuteSQL Name="SQL Recreate Pk_Index of Template" ConnectionName="LSDM20">
<DirectInput>EXEC [dbo].[sp_ExecDropAndRebuildSql]  ?, ?, 'create', 'p,i'</DirectInput>
<Parameters>
<Parameter Name="0" VariableName="User.SchemaName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129" ></Parameter>
<Parameter Name="1" VariableName="User.TableName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129" ></Parameter>
</Parameters>
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SRC setOnError.Output"></Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
</Tasks>
</Event>
<Event EventType="OnPostExecute" Name="OnPostExecute" ConstraintMode="Parallel">
<Tasks>
<Script  Name="SRC isPackageLevel" LoggingMode="Enabled" >
<ScriptTaskProject>
<ScriptTaskProject ScriptLanguage="CSharp" EntryPoint="Main" VstaMajorVersion="2" VstaMinorVersion="1" ProjectCoreName="Test" Name="Test Script">
<AssemblyReferences>
<AssemblyReference AssemblyPath="System"></AssemblyReference>
                
                <AssemblyReference AssemblyPath="System.Data"></AssemblyReference>
                <AssemblyReference AssemblyPath="System.Windows.Forms"></AssemblyReference>
                <AssemblyReference AssemblyPath="System.Xml"></AssemblyReference>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS"></AssemblyReference>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask"></AssemblyReference>

</AssemblyReferences>
<ReadOnlyVariables>
<Variable VariableName="SourceName" DataType="String" Namespace="System"></Variable>
<Variable VariableName="PackageName" DataType="String" Namespace="System"></Variable>
</ReadOnlyVariables>
<ReadWriteVariables>
<Variable VariableName="IsPackageLevel" DataType="Boolean" Namespace="User"></Variable>
</ReadWriteVariables>
<Files>

<File Path="ScriptMain.cs">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

    public void Main()
    {
if (Dts.Variables["System::SourceName"].Value.Equals(Dts.Variables["System::PackageName"].Value))
            {
                Dts.Variables["User::IsPackageLevel"].Value = true;
            }
            else
            {
                Dts.Variables["User::IsPackageLevel"].Value = false;
            } 
            Dts.TaskResult = (int)ScriptResults.Success;
    }
}
                </File>
                <File Path="AssemblyInfo.cs" BuildAction="Compile">using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyTitle("Test.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Microsoft")]
[assembly: AssemblyProduct("Test.csproj")]
[assembly: AssemblyCopyright("Copyright @ Microsoft 2013")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

[assembly: AssemblyVersion("1.0.*")]


</File>
</Files>
</ScriptTaskProject>
</ScriptTaskProject>
</Script>
<ExecuteSQL Name="SQL Update LSDM_AuditTask" ConnectionName="LSDM20">
<DirectInput> UPDATE [dbo].[AuditTask] WITH(RowLock) 
SET [Status] = 'Completed'
,[EndTime] = GETDATE()
,[RowsAffected]= 0
,[LastModDate]=''
Where AuditTaskId= '0'
</DirectInput>
<Expressions>
<Expression PropertyName="SqlStatementSource">"UPDATE [dbo].[AuditTask] WITH(RowLock) 
SET [Status] = '"+@[User::Status]+"'
,[EndTime] = GETDATE()
,[RowsAffected]= "+ (DT_WSTR, 20)( @[User::RowsAffected]) +"
,[LastModDate]='"+ @[User::LastModDate] +"'
Where AuditTaskId= '"+ (DT_WSTR, 20)(@[User::AuditTaskId])  +"'
GO"</Expression>
</Expressions>
<PrecedenceConstraints LogicalType="And">
<Inputs>
<Input EvaluationOperation="ExpressionAndConstraint"  
  OutputPathName="SRC isPackageLevel.Output"
  EvaluationValue="Success" 
  Expression="!@IsPackageLevel">
</Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
<ExecuteSQL Name="SQL Update LSDM_Audit" ConnectionName="LSDM20">
<DirectInput> UPDATE [dbo].[Audit]
  WITH(RowLock) SET [Status] = 'Completed'
,[EndTime] = GETDATE()
Where AuditId= -1 
GO 
</DirectInput>
<Expressions>
<Expression PropertyName="SqlStatementSource">"UPDATE [dbo].[Audit]
WITH(RowLock) SET [Status] = '"+ @[User::Status] +"'
,[EndTime] = GETDATE()
Where AuditId= "+ (DT_WSTR, 20) (@[User::AuditId]) +" 
GO
"</Expression>
</Expressions>
<PrecedenceConstraints LogicalType="And">
<Inputs>
<Input EvaluationOperation="ExpressionAndConstraint"  
  OutputPathName="SRC isPackageLevel.Output"
  EvaluationValue="Success" 
  Expression="@IsPackageLevel &amp;&amp; !@IsParentValue">
</Input>

</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
</Tasks>
</Event>
<!-- Pre Execute-->
<Event EventType="OnPreExecute" Name="OnPreExecute" ConstraintMode="Parallel">
<Tasks>
<Script  Name="SRC InitialLevel" LoggingMode="Enabled" >
<ScriptTaskProject>
<ScriptTaskProject ScriptLanguage="CSharp" EntryPoint="Main" VstaMajorVersion="2" VstaMinorVersion="1" ProjectCoreName="Test" Name="Test Script">
<AssemblyReferences>
<AssemblyReference AssemblyPath="System"></AssemblyReference>
                
                <AssemblyReference AssemblyPath="System.Data"></AssemblyReference>
                <AssemblyReference AssemblyPath="System.Windows.Forms"></AssemblyReference>
                <AssemblyReference AssemblyPath="System.Xml"></AssemblyReference>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS"></AssemblyReference>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask"></AssemblyReference>

</AssemblyReferences>
<ReadOnlyVariables>
<Variable VariableName="SourceName" DataType="String" Namespace="System"></Variable>
<Variable VariableName="PackageName" DataType="String" Namespace="System"></Variable>
</ReadOnlyVariables>
<ReadWriteVariables>
<Variable VariableName="IsPackageLevel" DataType="Boolean" Namespace="User"></Variable>
</ReadWriteVariables>
<Files>

<File Path="ScriptMain.cs">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

    public void Main()
    {
if (Dts.Variables["System::SourceName"].Value.Equals(Dts.Variables["System::PackageName"].Value))
            {
                Dts.Variables["User::IsPackageLevel"].Value = true;
            }
            else
            {
                Dts.Variables["User::IsPackageLevel"].Value = false;
            } 
            Dts.TaskResult = (int)ScriptResults.Success;
    }
}
                </File>
                <File Path="AssemblyInfo.cs" BuildAction="Compile">using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyTitle("Test.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Microsoft")]
[assembly: AssemblyProduct("Test.csproj")]
[assembly: AssemblyCopyright("Copyright @ Microsoft 2013")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

[assembly: AssemblyVersion("1.0.*")]


</File>
</Files>
</ScriptTaskProject>
</ScriptTaskProject>
</Script>
<ExecuteSQL Name="SQL Insert LSDM_AuditTask" ConnectionName="LSDM20">
<DirectInput> exec spu_insert_audit_task ?,?,?,?,?,?,?,?,? OUT</DirectInput>
<Parameters>
<Parameter Name="0" VariableName="User.AuditId" DataType="Int32" Direction="Input" Length="-1" ></Parameter>
<Parameter Name="8" VariableName="User.AuditTaskId" DataType="Int32" Direction="Output" Length="-1"></Parameter>
<Parameter Name="1" VariableName="System.StartTime" DataType="Date" Direction="Input" Length="-1"></Parameter>
<Parameter Name="2" VariableName="System.VersionBuild" DataType="Int32" Direction="Input" Length="-1"></Parameter>
<Parameter Name="3" VariableName="System.UserName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>
<Parameter Name="4" VariableName="System.PackageName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>
<Parameter Name="5" VariableName="System.MachineName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>
<Parameter Name="6" VariableName="System.SourceName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>
<Parameter Name="7" VariableName="User.TableName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>

</Parameters>

<PrecedenceConstraints LogicalType="And">
<Inputs>
<Input EvaluationOperation="ExpressionAndConstraint"  
  OutputPathName="SRC InitialLevel.Output"
  EvaluationValue="Success" 
  Expression="!(SUBSTRING(@[System::SourceName],1,4) == &quot;SEQC&quot; ) &amp;&amp; !@IsPackageLevel">
</Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
<ExecuteSQL Name="SQL Insert LSDM_Audit" ConnectionName="LSDM20">
<DirectInput> exec spu_insert_audit ?,?, ?,?,?,? OUT </DirectInput>
<Parameters>
<Parameter Name="5" VariableName="User.AuditId" DataType="Int32" Direction="Output" Length="-1" ></Parameter>
<Parameter Name="4" VariableName="System.MachineName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>
<Parameter Name="0" VariableName="System.StartTime" DataType="Date" Direction="Input" Length="-1"></Parameter>
<Parameter Name="1" VariableName="System.VersionBuild" DataType="Int32" Direction="Input" Length="-1"></Parameter>
<Parameter Name="2" VariableName="System.UserName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>
<Parameter Name="3" VariableName="System.PackageName" DataType="AnsiString" Direction="Input" Length="-1" DataTypeCodeOverride="129"></Parameter>


</Parameters>
<PrecedenceConstraints LogicalType="And">
<Inputs>
<Input EvaluationOperation="ExpressionAndConstraint"  
  OutputPathName="SRC InitialLevel.Output"
  EvaluationValue="Success" 
  Expression="@IsPackageLevel &amp;&amp; !@IsParentValue">
</Input>

</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
</Tasks>
</Event>
<Event EventType="OnTaskFailed" Name="OnTaskFailed" ConstraintMode="Parallel">
<Tasks>
<Script  Name="SRC setOnTaskFailed" LoggingMode="Enabled" >

<ScriptTaskProject>
<ScriptTaskProject ScriptLanguage="CSharp" EntryPoint="Main" VstaMajorVersion="2" VstaMinorVersion="1" ProjectCoreName="Test" Name="Test Script">
<AssemblyReferences>
<AssemblyReference AssemblyPath="System"></AssemblyReference>                
<AssemblyReference AssemblyPath="System.Data"></AssemblyReference>
<AssemblyReference AssemblyPath="System.Windows.Forms"></AssemblyReference>
<AssemblyReference AssemblyPath="System.Xml"></AssemblyReference>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS"></AssemblyReference>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask"></AssemblyReference>
</AssemblyReferences>
<ReadWriteVariables>
<Variable VariableName="Status" DataType="String" Namespace="User"></Variable>
</ReadWriteVariables>
<Files>

<File Path="ScriptMain.cs">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

    public void Main()
    {
Dts.Variables["User::Status"].Value = "OnTaskFailed";
Dts.TaskResult = (int)ScriptResults.Success;
    }
}
                </File>
                <File Path="AssemblyInfo.cs" BuildAction="Compile">using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyTitle("Test.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Microsoft")]
[assembly: AssemblyProduct("Test.csproj")]
[assembly: AssemblyCopyright("Copyright @ Microsoft 2013")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

[assembly: AssemblyVersion("1.0.*")]


</File>
</Files>
</ScriptTaskProject>
</ScriptTaskProject>
</Script>
</Tasks>
</Event>

</Events>

<Tasks>
<ExecuteSQL Name="SQL Get IsInitialLoad" ConnectionName="LSDM20">
<DirectInput>SELECT ? = isnull(IsInitialLoad, 1) from dbo.InitialLoad </DirectInput>
<Parameters>
<Parameter VariableName="User.IsInitialLoad" Name="IsInitialLoad" DataType="Int32" Direction="Output" ></Parameter>
</Parameters>
</ExecuteSQL>

<ExecuteSQL Name="SQL Get MaxAuditId" ConnectionName="EDW20">
<DirectInput>Truncate table Template_Aprimo</DirectInput>
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Get IsInitialLoad.Output" EvaluationOperation="ExpressionAndConstraint" EvaluationValue="Success" Expression="@IsInitialLoad == 0"></Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
<ExecuteSQL Name="SQL Truncate" ConnectionName="LSDM20">
<DirectInput>Truncate table Template_Aprimo</DirectInput>
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Get IsInitialLoad.Output" EvaluationOperation="ExpressionAndConstraint" EvaluationValue="Success" Expression="@IsInitialLoad == 1"></Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
<ExecuteSQL Name="SQL Merge Template Incrimental" ConnectionName="LSDM20">
<DirectInput>Truncate table Template_Aprimo</DirectInput>
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Get MaxAuditId.Output"></Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
<Dataflow Name="DFT Load LSDM_Template_Aprimo">
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Truncate.Output"></Input>
</Inputs>
</PrecedenceConstraints>
<Transformations>
<OleDbSource Name="OLE_SRC Aprimo" ConnectionName="LSDM20">
<DirectInput>SELECT ActivityAprimoID, ActivityID, ActivityTitle, Department, ActivityOwner FROM dbo.s_Template_Aprimo </DirectInput>

<Columns>
<Column SourceColumn="ActivityAprimoID" TargetColumn="ActivityAprimoID" />
<Column SourceColumn="ActivityID" TargetColumn="ActivityID" />
<Column SourceColumn="ActivityTitle" TargetColumn="ActivityTitle" />
<Column SourceColumn="Department" TargetColumn="Department" />
<Column SourceColumn="ActivityOwner" TargetColumn="ActivityOwner" />
</Columns>
</OleDbSource>
<DataConversion Name="DCR">
<Columns>
<Column SourceColumn="ActivityTitle" TargetColumn="ActivityTitle" DataType="AnsiString" Length="275"  />
<Column SourceColumn="Department" TargetColumn="Department" DataType="AnsiString" Length="100" />
<Column SourceColumn="ActivityOwner" TargetColumn="ActivityOwner" DataType="AnsiString" Length="75"/>
</Columns>
</DataConversion>
<DerivedColumns Name="DER AuditTaskId">
<InputPath OutputPathName="DCR.Output"></InputPath>
<Columns>
<Column Name="AuditTaskId" DataType="Int32">@[User::AuditTaskId]</Column>
</Columns>
</DerivedColumns>
<RowCount VariableName="User.RowsAffected" Name="Row Count">
<InputPath OutputPathName="DER AuditTaskId.Output"></InputPath>
</RowCount>
<OleDbDestination Name="OLE_DST ListSelect20_State_Template_Aprimo" ConnectionName="LSDM20">
<ExternalTableOutput Table="dbo.Template_Aprimo" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>

</Package>
</Packages>
</Biml>