BIML script to create package in staging level

Truncate and Load with connection managers, configuration, variables and EventHandler


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="Imports20" ConnectionString="Data Source=<servername>;Initial Catalog=Imports20;Provider=SQLNCLI11.1;OLE DB Services=1;Integrated Security=SSPI;" />
<Connection Name="Aprimo" ConnectionString="Data Source=<servername>;Initial Catalog=Aprimo;Provider=SQLNCLI11.1;OLE DB Services=1;Integrated Security=SSPI;" />
</Connections>
<Packages>
<Package Name="Imports20_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>
</Variables>
<!-- Package Configurations -->
<!--
<PackageConfigurations>
<PackageConfiguration Name="Imports20_Connection">
<EnvironmentVariableInput EnvironmentVariable="Imports_ConnectionConfig_Path"></EnvironmentVariableInput>
<ConfigurationValues>
<ConfigurationValue DataType="String" PropertyPath="\Package.Connections[Imports20].Properties[ConnectionString]" 
Name="Imports20" 
Value="Data Source=<servername>;Inital Catalog=Imports20;Provider=SQLNCLI10.1;Integrated Security=SSPI;">
</ConfigurationValue>
</ConfigurationValues>
</PackageConfiguration>
<PackageConfiguration Name="Aprimo_Connection">
<EnvironmentVariableInput EnvironmentVariable="Aprimo_ConnectionConfig_Path"></EnvironmentVariableInput>
<ConfigurationValues>
<ConfigurationValue DataType="String" PropertyPath="\Package.Connections[Aprimo].Properties[ConnectionString]" 
Name="Aprimo" 
Value="Data Source=<servername>;Inital Catalog=Aprimo;Provider=SQLNCLI10.1;Integrated Security=SSPI;">
</ConfigurationValue>
</ConfigurationValues>
</PackageConfiguration>
</PackageConfigurations>
-->
<PackageConfigurations>

<PackageConfiguration Name="Imports20_Connection">
<IndirectFileInput EnvironmentVariable="Imports_ConnectionConfig_Path"></IndirectFileInput>
</PackageConfiguration>
<PackageConfiguration Name="Aprimo_Connection">
<IndirectFileInput EnvironmentVariable="Aprimo_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>
</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 Imports_AuditTask" ConnectionName="Imports20">
<DirectInput> UPDATE [dbo].[AuditTask] SET [Status] = 'Completed'
,[EndTime] = GETDATE(),[RowsAffected]= 0,[LastModDate]=''Where AuditTaskId= '0'
</DirectInput>
<Expressions>
<Expression PropertyName="SqlStatementSource">"UPDATE [dbo].[AuditTask]
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 Imports_Audit" ConnectionName="Imports20">
<DirectInput> UPDATE [dbo].[Audit] SET [Status] = 'Completed', [EndTime] = GETDATE()
Where AuditId= -1 
</DirectInput>
<Expressions>
<Expression PropertyName="SqlStatementSource">"UPDATE [dbo].[Audit]
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 Imports_AuditTask" ConnectionName="Imports20">
<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;FELC&quot; ) &amp;&amp; !(SUBSTRING(@[System::SourceName],1,3)==&quot;SRC&quot;) &amp;&amp; !@IsPackageLevel">
</Input>
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
<ExecuteSQL Name="SQL Insert Imports_Audit" ConnectionName="Imports20">
<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="Truncate Template_Aprimo" ConnectionName="Imports20">
<DirectInput>Truncate table Template_Aprimo</DirectInput>
</ExecuteSQL>
<Dataflow Name="Load Aprimo">
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="Truncate Template_Aprimo.Output"></Input>
</Inputs>
</PrecedenceConstraints>
<Transformations>
<OleDbSource Name="OLE_SRC Aprimo" ConnectionName="Aprimo">
<!--<ExternalTableInput  Table="dbo.vwAprimoActivity" />-->
<DirectInput>SELECT ActivityID, ActivityTitle, Department, ActivityOwner FROM dbo.vwAprimoActivity </DirectInput>

<Columns>
<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>
<RowCount VariableName="User.RowsAffected" Name="Row Count">
<InputPath OutputPathName="DCR.Output"></InputPath>
</RowCount>
<OleDbDestination Name="OLE_DST Imports20_Template_Aprimo" ConnectionName="Imports20">
<ExternalTableOutput Table="dbo.Template_Aprimo" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>

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