Thursday, July 24, 2014

To send email when package failed or successed

Create user(project) variables to have exchange server name, from email id, to email id and subject.

 Get script task in evenhandler at OnError event. Select the below mentioned varibles in Read only variable option and apply the script

 System::ErrorDescription
System::SourceName
System::PackageName
System::StartTime

 $Project::ExchangeServerName
$Project::MailFrom
$Project::MailTo
$Project::Subject

 //required namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;
using System.Text;

 public void Main()
{
// TODO: Add your code here
            string MessageTo = Dts.Variables["$Project::MailTo"].Value.ToString();
            string MessageFrom = Dts.Variables["$Project::MailFrom"].Value.ToString();
            DateTime StartTime = (DateTime)Dts.Variables["System::StartTime"].Value;
            string packagename = Dts.Variables["System::PackageName"].Value.ToString();
            string MessageSubject = packagename + " " + "Package Failed";
            string sourcename = Dts.Variables["System::SourceName"].Value.ToString();
            string MessageBody = Dts.Variables["System::ErrorDescription"].Value.ToString();
            string smtpServer = Dts.Variables["$Project::ExchangeServerName"].Value.ToString();

            SendMailMessage(MessageTo, MessageFrom, StartTime, packagename, MessageSubject, sourcename, MessageBody, true, smtpServer);

 Dts.TaskResult = (int)ScriptResults.Success;
}
        private void SendMailMessage(string SendTo, string From, DateTime Starttime, string Subject, string packagename, string sourcename, string Body, bool IsBodyHtml, string Server)
        {

             MailMessage htmlMessage;
            SmtpClient mySmtpClient;

             htmlMessage = new MailMessage(SendTo, From, Subject, Body);
            StringBuilder mailstring = new StringBuilder();
            mailstring.Append("<html><body>" + "<table border='1' width='100%'> <tr border=2 bgcolor='gray'><td border=2>Start Time</td><td>Package Name</td><td>Source Name</td><td>Error Description</td></tr><tr> <td border=2>"
            + Starttime + "</td><td>" +packagename + "</td><td>" + sourcename + "</td><td>" + Body + "</td></tr> </table></body></html>");
           

             mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
           
            htmlMessage.IsBodyHtml = true;
            htmlMessage.Body = mailstring.ToString();
            mySmtpClient.Send(htmlMessage);
        }
Posted by at No comments:
Labels: ,

Database Management

To view the database size: I have used 3 types of option to view database size:

 EXEC sp_spaceused

 SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'TestDB'
GO

 SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

 Some of the Maintenance DBCC Statements are:
DBCC CLEANTABLE

 DBCC INDEXDEFRAG (databasename, tablename,indexname)
defragment clustered and nonclustered indexes on tables and views

 DBCC DBREINDEX(tablename)
rebuilds an index for a table or all indexes defined for a table.

 DBCC SHRINKDATABASE(databasename)
Shrinks the size of the data and log files in the specified database
Avoid executing this command during busy periods in production

 DBCC SHRINKFILE(file_id) Use exec sp_helpfile
allows you to shrink the size of individual data and log files
Avoid, as in most cases the database will just regrow and shrinking data files causes fragmentation.

 DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC SHRINKFILE
DBCC UPDATEUSAGE

 Some of the Miscellaneous DBCC Statements are:
DBCC dllname (FREE)
DBCC HELP
DBCC FREESESSIONCACHE 
DBCC TRACEOFF - used to disable tracing
DBCC FREESYSTEMCACHE
DBCC TRACEON - used to enable tracing

 Some of the Informational DBCC Statements are:
DBCC INPUTBUFFER(sessionid)
to view the last statement sent by the client connection to SQL Server

 DBCC SHOWCONTIG
DBCC OPENTRAN

 DBCC SQLPERF(logspace)
To see transaction log size of each database on Server

 DBCC SHOWFILESTATS
Show Total Extents and used extents for database

 DBCC OUTPUTBUFFER

 DBCC TRACESTATUS
used to know trace status with TraceFlag,Status,Global,Session

 DBCC PROCCACHE

 DBCC USEROPTIONS
Returns the SET options active (set) for the current connection

 DBCC SHOW_STATISTICS
Some of the Validation DBCC Statements are:
DBCC CHECKALLOC
checks page usage and allocation in the database.

 DBCC CHECKFILEGROUP

 DBCC CHECKCATALOG
Checks for catalog/tables consistency within the specified database

 DBCC CHECKIDENT

 DBCC CHECKCONSTRAINTS 
Checks the integrity of a specified constraint or all constraints on a specified table in the current database

DBCC CHECKTABLE(tablename)
It verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets on table.

 DBCC CHECKDB

 T-SQL Query to find all members in server role:

 SELECT SUSER_NAME(members.role_principal_id) AS [ServerRole]
   ,logins.name AS 'RoleMember'
   ,'EXEC sp_addsrvrolemember ''' +logins.name+''', '''+
   SUSER_NAME(members.role_principal_id)+'''' AS [Command to add role members]
FROM sys.server_role_members members, sys.server_principals logins
WHERE members.role_principal_id >=3 AND members.role_principal_id <=10 AND
members.member_principal_id = logins.principal_id
--and logins.name <>'sa' --Excluding system administrator

 T-SQL to get the list of objects modified in x number of days:

 DECLARE @Days int
SET @Days=300 -- Specify the number of days
SELECT name AS ObjectName
  ,SCHEMA_NAME(schema_id) AS SchemaName
  ,type_desc AS ObjectType
  ,create_date AS ObjectCreatedOn
  ,modify_date As ObjectModifiedOn
FROM sys.objects
WHERE modify_date > GETDATE() - @Days
ORDER BY modify_date;
GO


Posted by at No comments:
Labels:

Monday, July 7, 2014

Retrieve data from Distimo - Mobile Application

Below is the code to retrieve mobile application downloads data from Distimo API

public void Main()
{
// Returns assets in json format
string api = "https://api.distimo.com/v4/downloads";

DateTime FromDate = (DateTime)Dts.Variables["User::FromDate"].Value;
string s = FromDate.ToString("yyyy-MM-dd");
DateTime ToDate = (DateTime)Dts.Variables["User::ToDate"].Value;
string s1 = ToDate.ToString("yyyy-MM-dd");


//string query = "format=csv&from=2010-12-01&to=2010-12-31&breakdown=application,appstore";
string query = "format=csv" + "&" + "from=" + s + "&" + "to=" + s1 + "&breakdown=application,appstore";

// Necessary variables

string publicKey = (string)Dts.Variables["User::PublicKey"].Value;
string privateKey = (string)Dts.Variables["User::PrivateKey"].Value;
string userName = (string)Dts.Variables["User::UserName"].Value;
string password = (string)Dts.Variables["User::Password"].Value;

// Identify application
int time = (int)(DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0)).TotalSeconds;
string data = String.Concat(query, time);
HMACSHA1 hmac = new HMACSHA1(Encoding.ASCII.GetBytes(privateKey));
hmac.Initialize();

byte[] buffer = Encoding.ASCII.GetBytes(data);
string hash = BitConverter.ToString(hmac.ComputeHash(buffer)).Replace("-", "").ToLower();
string user = String.Concat(userName, ":", password);
string base64Login = Convert.ToBase64String(Encoding.Default.GetBytes(user));

// Make the request
string url = api + "?" + query + "&apikey=" + publicKey + "&hash=" + hash + "&t=" + time;
WebRequest request = WebRequest.Create(url);
request.Headers["Authorization"] = String.Concat("Basic ", base64Login);

//MessageBox.Show(url);

string result;
try
{
result = new StreamReader(request.GetResponse().GetResponseStream()).ReadToEnd();
}
catch (WebException e)
{
result = new StreamReader(e.Response.GetResponseStream()).ReadToEnd();
}

System.IO.File.WriteAllText(@"D:\MobileApp_Distimo\MobileApp_Downloads.txt", result);

Dts.TaskResult = (int)ScriptResults.Success;
}


Posted by at No comments:
Labels:

Update values in multiple tables using single script

Below is the script used to update values in multiple tables. This is the script I used to update according to my requirement. Kindly modify it as needed.

 DECLARE @oldCompanyid INT, @newcompanyid INT, @oldCompanyCode VARCHAR(50) 

        DECLARE @t Table(Name VARCHAR(100), MaxId INT)
        INSERT INTO @t(Name, MaxId)
        SELECT TableName ,maxAuditTaskId FROM dbo.AuditTaskSummary WHERE TableName = 'SourceCode' and PackageName = 'pkg_Sourcecode'
        
        SELECT @oldCompanyid = @CompanyIdOld --7
        SELECT @newcompanyid = 3
        
        CREATE TABLE #t1(RowNum INT, TableName VARCHAR(100), QueryData VARCHAR(1000))
        DECLARE @maxRow int, @minRow int
        INSERT INTO #t1 (RowNum, TableName, QueryData)
        SELECT ROW_NUMBER () OVER(ORDER BY T.name DESC) RowNum,T.name AS tableName, 
        'UPDATE ' + SCHEMA_NAME(t.schema_id) + '.' 
        + T.NAME + ' SET CompanyID  = '+CAST (@newcompanyid AS VARCHAR(10)) + ',' 
        + 'Audittaskid =' +CAST (ATS.maxAuditTaskId+1 AS VARCHAR(10)) 
        + ' WHERE CompanyID='+ CAST(@oldCompanyid AS VARCHAR(10)) +';' AS QueryData
        FROM sys.tables as T
        INNER JOIN sys.COLUMNS C ON t.OBJECT_ID = c.OBJECT_ID
        INNER JOIN (SELECT TableName ,maxAuditTaskId FROM dbo.AuditTaskSummary WHERE TableName NOT IN ('SourceCode') 
        UNION 
        SELECT Name, maxid FROM @t) ATS ON ATS.TableName = T.name
        WHERE c.name ='CompanyID' AND SCHEMA_NAME(t.schema_id) = 'dbo' 
        AND t.name NOT IN ('Company','Company_backup')
        ORDER BY SCHEMA_NAME(t.schema_id), t.name;

 SELECT * FROM #t1
SELECT @maxRow = (SELECT MAX(RowNum) FROM #t1),  @minRow = (SELECT MIN(RowNum) FROM #t1)
SELECT @maxRow, @minRow

 WHILE @maxRow >= @minRow
BEGIN
DECLARE @tablevalue VARCHAR(1000), @tablename VARCHAR(100)
SELECT @tablevalue = (SELECT QueryData from #t1 WHERE RowNum = @minRow), @tablename = (SELECT tablename FROM #t1 WHERE RowNum = @minRow)

 IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename 
AND COLUMN_NAME = 'CompanyId')
EXEC (@tablevalue);
SET @minRow = @minRow +1
END
DROP TABLE #t1;
Posted by at No comments:
Labels: ,

Calling the case condition inside the partition using row_number

,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY CASE [status]
WHEN 'active' THEN 1
WHEN 'new' THEN 2
WHEN 'cancelled' THEN 3
ELSE 4
END) AS RN
Posted by at No comments:
Labels: ,

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>

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)