SQL Geometry Data Type example

Using Multi-Value Parameter in SSRS via Stored Procedure

It has been found that in SSRS 2005-2012, when we use direct query as data set we are able to apply multi-value parameter and the report works when we select multiple values in parameter.

E.g., Let the direct query be as follows:

SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country in (@Country)

When we use the above query in dataset a parameter Country will be created in Parameter list, and we can configure parameter as shown below, the report work well when we select multiple values in parameter.

When the same query is executed as stored procedure, the report will fail when we select multiple values in parameter. To overcome this issue we need to create the given function in our database:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MultiParameter]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

And we need to modify the stored procedure as like below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  BabukannanM
-- Create date: September 19, 2012
-- Description: To list of Customer by Countrywise
-- =============================================
CREATE PROCEDURE [dbo].[Rpt_Customer]
 @Country NVARCHAR(MAX)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
  WHERE Country IN (Select * from [dbo].[MultiParameter] (@Country, ',') )

END
GO

 Now configure the report parameter to select multi-values.

How to get customized colors in SSRS chart

While we design SSRS chart, we have to use available colors from SSRS Color pallette or color templates by selecting them from color, background color, etc. Instead we can apply our own defined colors to our chart by following below steps:

Step 1: Create new SSRS report.

Step 2: Go to Report properties of the SSRS report. Open Code section and copy-paste the below code in the Code area:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0

Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String

If mapping.ContainsKey(groupingValue) Then

Return mapping(groupingValue)

End If

Dim c As String = colorPalette(count Mod colorPalette.Length)

count = count + 1

mapping.Add(groupingValue, c)

Return c

End Function

Step 3: Now create a chart (e.g., pie chart); Open Series properties, in Fill expression copy-paste below command:

=code.GetColor(Fields!SalesMonth.Value)


Step 4 (Option): You can use this color in your grid report as a background, by copy-paste the below code in Columns Background properties:

=code.GetColor(Fields!SalesMonth.Value)

The output will be simillar to one below:

Create SSRS Report in Web Page using ASP.NET

The following steps help to create an SSRS report in Web Page using ASP.NET. We should install AJAX toolkit to practice the code.

1. Create a new ASP wep page project.

2. Add a new web form.

3. Add the below scripts in the WEB for designer:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SSRS2.aspx.cs" Inherits="SSRS_WEB.WebForm2" %>

<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<table> 

<tr> 

<td>Start Date: </td> 

<td><asp:TextBox Width="180" runat="server" ID="StartDatePr"/></td>

<td><asp:CalendarExtender runat="server" TargetControlID="StartDatePr"/></td>

<td>End Date: </td> 

<td><asp:TextBox Width="180" runat="server" ID="EndDatePr"/></td>

<td><asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="EndDatePr"/></td>

<td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td> 

</tr> 

</table> 

</div>

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

</asp:ToolkitScriptManager>

<rsweb:ReportViewer ID="MyReportViewer" runat="server" Font-Names="Verdana" 

Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote" 

WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="800px" 

Width="1000px">

<ServerReport ReportServerUrl="" />

</rsweb:ReportViewer>

</form>

</body>

</html>

4. Add below code in respective .CS file:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Microsoft.Reporting.Common;

using Microsoft.Reporting.WebForms;

namespace SSRS_WEB

{

public partial class WebForm2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

//First 

MyReportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver_Sathya"); // Report Server URL

MyReportViewer.ServerReport.ReportPath = "/SQLSSRS/Dashboard"; // Report Name

MyReportViewer.ShowParameterPrompts = false;

MyReportViewer.ShowPrintButton = false;

ReportParameter[] parameters = new ReportParameter[2];

parameters[0] = new ReportParameter("StartDate", StartDatePr.Text);

parameters[1] = new ReportParameter("EndDate", EndDatePr.Text);

MyReportViewer.ServerReport.SetParameters(parameters);

MyReportViewer.ServerReport.Refresh();

}

}

}

On executing the page, you can see the SSRS reoport created in Web page.

Using CHARINDEX in SQL Query

CHARINDEX returns a starting position of a string which helps developer to split a data based on the requirement.


DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange

Result:

MinRange         MaxRange
    50                      70

Get Hierarchial Level of Employee Using SQL

Create a new table as shown below:

CREATE TABLE [dbo].[Employee](
                    [EmployeeID] [int] NOT NULL,
                    [EmpName] [varchar](100) NULL,
                    [Department] [varchar](100) NULL,
                    [Designation] [varchar](100) NULL,
                    [Salary] [money] NULL,
                    [ManagerID] [int] NULL
    ) ON [PRIMARY]

Insert valuse to the table as required and apply the below script:

SELECT L3.EmpName AS LEVEL2, L2.EmpName LEVEL1, L1.EmpName LEVEL0
    ,CAST(ISNULL(L3.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmployeeID,'') AS VARCHAR(5)) AS LEVELPATH
,CAST(ISNULL(L3.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmpName,'') AS VARCHAR(5)) AS EMPLOYETREE
FROM Employee L1
LEFT JOIN Employee L2 ON L1.ManagerID = L2.EmployeeID
LEFT JOIN Employee L3 ON L2.ManagerID = L3.EmployeeID
LEFT JOIN Employee L4 ON L3.ManagerID = L4.EmployeeID
LEFT JOIN Employee L5 ON L4.ManagerID = L5.EmployeeID

Below is the result:

GPG File encryption Commands

GPG File encryption Commands

First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system:

TO ENCRYPT FILE

C:\Program Files (x86)\GNU\GnuPG>gpg -e -r "name" filepath

Here 'name' represents the username obtained while generating GPG key.

E.g., C:\Program Files (x86)\GNU\GnuPG>gpg --yes --always-trust  --encrypt --recipient Babukannan C:\Users\ Babukannan \Desktop\XML\FOTF.XML

TO DECRYPT FILE

 C:\Program Files (x86)\GNU\GnuPG>gpg --yes --always-trust --passphrase xxxxxx --output C:\Users\ Babukannan  \Desktop\XML\FOTFB.XML -d C:\Users\ Babukannan  \Desktop\XML\FOTF.XML.GPG

Here 'xxxxx' represents the password obtained while generating GPG key.


TO GENERATE GPG KEY

Follow the steps in the below image:

TO VIEW THE GPG KEYS

C:\Program Files (x86)\GNU\GnuPG>gpg --List-keys


TO EDIT THE GPG KEYS

C:\Program Files (x86)\GNU\GnuPG>gpg --Edit-keys

GPG File Encryption in SSIS

Using GPG File Encryption in SSIS

Step 1: First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system.

Step 2: Create a new ssis package.

Step 3: Add ExecuteProcessTask. 

Step 4: To encrypt a file, configure Execute Process task as shown below:

Executables: C:\Program Files (x86)\GNU\GnuPG\gpg.exe

Arguments: --yes --always-trust  --recipient Masterworks --output  Filename.GPG --encrypt Filename.txt
WorkingDirectory: D:\File Path....

Step 4A: To decrypt a file, add below code in arguments:

--yes --always-trust --passphrase xxxxxx --output Filename.txt -d Filenname.GPG

Enter your GPG password in place of xxxxxxx .

Scenario - Updating a column based on the other column's value using Dense_Rank function

Requirement:

There should only be one row for any given TransactionId that has a 1 in the ResponseCount. All other rows should have a 0.

In cases where a response is split among several rows, add a 1 to the row based on the transaction type hierarchy below:
1) Donation
2) Subscription
3) Product
4) Event
5) Non Cash
6) Communcation

The top ranking row will get a 1 while the other(s) will get a 0.

In cases with only one row, it will of course get a 1

If there are multiple rows within any of the 1-6 categories above, select the highest dollar row first to get the ResponseCount 1 value, else select the first row.

Solution:

Create a table "dbo.TransactionDetailGiftTest" as shown below:


CREATE TABLE [dbo].[TransactionDetailGiftTest](
[TransactionHistoryKey] [int] NOT NULL,
[DSTransactionId] [int] NOT NULL,
[Type] [varchar](20) NOT NULL,
[TransactionDate] [date] NULL,
[FiscalYear] [int] NULL,
[FiscalMonth] [int] NULL,
[TotalAmount] [money] NULL,
[ResponseCount] [int] NULL,
 CONSTRAINT [PK_TransactionDetailGiftTest] PRIMARY KEY CLUSTERED
(
[TransactionHistoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Also create a Dimenstion table "dbo.dim" for type hierarchy 

CREATE TABLE [dbo].[Dim](

[ResponseCountKey] [int] IDENTITY(1,1) NOT NULL,

[Type] [varchar](20) NOT NULL,

 CONSTRAINT [PK_Dim] PRIMARY KEY CLUSTERED 

(

[ResponseCountKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now write the below mention script in order to achieve the result

WITH CTE AS (SELECT Responsecount, DENSE_RANK()

OVER (PARTITION BY dstransactionid ORDER BY td.responsecountkey, totalamount DESC) AS Result

FROM dbo.TransactionDetailGiftTest tt

INNER JOIN dbo.dim td on tt.[Type] = td.[type])

UPDATE CTE

SET ResponseCount = Result

UPDATE dbo.TransactionDetailGiftTest

SET ResponseCount = 0

WHERE responsecount > 1 

Now the table should be updated as required: Run the query to check the table:

SELECT * FROM dbo.TransactionDetailGiftTest

This scenario helps to understand a logic for the similar kind of requirement.

Configure Change Data Capture (CDC) Parameters in SQL Server

After enabling CDC in SQL Server, We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). 

The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs

Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'

       ,@maxtrans = 501

       ,@maxscans = 10

       ,@continuous = 1

       ,@pollinginterval = 5

Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'

                          ,@retention = 4320 -- Number of minutes to retain (72 hours)

                          ,@threshold = 5000

Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc. 

Convert the year, month and day column as date format

Below is the script to convert the year, month and day column as date format

SELECT
CAST(
CAST(year AS VARCHAR(4)) +
RIGHT('0' + CAST(month AS VARCHAR(2)),2) +
RIGHT('0' + CAST(day AS VARCHAR(2)),2)
AS DATE )
FROM [dbo].[SampleTable]

Finding NULL values from all the tables in a database using single script

Script to find NULL values from all the tables in Database


USE [NULLTest]  --database name
GO

create table #SuspectColumns (
    TABLE_SCHEMA sysname,
    TABLE_NAME sysname,
    COLUMN_NAME sysname
)

declare csrColumns cursor fast_forward for
    select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
        from INFORMATION_SCHEMA.COLUMNS
        where IS_NULLABLE = 'YES'

declare @TABLE_SCHEMA sysname,
        @TABLE_NAME sysname,
        @COLUMN_NAME sysname,
        @sql nvarchar(max)

open csrColumns

while (1=1) begin
    fetch next
        from csrColumns
        into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME

    if @@FETCH_STATUS<>0 break

    set @sql = N'if exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is null)
                     insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')'

    exec sp_executesql @sql
end /* while */

close csrColumns
deallocate csrColumns

select * from #SuspectColumns

drop table #SuspectColumns

below are the example tables which contain NULLs

following is the result while run the above script

Slowly Changing Dimension in SSIS

The "Slowly Changing Dimension" problem is a common one particular to data warehousing. This applies to cases where the attribute for a record varies over time. 

We give an example below:

Babu is a customer with XYZ Inc. He first lived in Chicago, Illinois. So, the original entry in the customer 

lookup table has the following record:

Customer Key	Name	State
1001	Babu	Illinois

At a later date, He moved to Los Angeles, California on January, 2003. How should XYZ Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

There are in general three ways to solve this type of problem, and they are categorized as follows:

Type 1: The new record replaces the original record. No trace of the old record exists.

Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

Type 3: The original record is modified to reflect the change.

We next take a look at each of the scenarios and how the data model and the data looks like for each of them. Finally, we compare and contrast among the three alternatives.

In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.
In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Babu	Illinois

After Babu moved from Illinois to California, the new information replaces the new record, and we have the following table:

Customer Key	Name	State
1001	Babu	California

Advantages:
- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.
Disadvantages:
- All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.

When to use Type 1:
Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.
In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Babu	Illinois

After Babu moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Babu	Illinois
1005	Babu	California

Advantages:
- This allows us to accurately keep all historical information.
Disadvantages:
- This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
- This necessarily complicates the ETL process.

When to use Type 2:
Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.
In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Babu	Illinois

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

• Customer Key
• Name
• Original State
• Current State
• Effective Date

After Babu moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

Customer Key	Name	Original State	Current State	Effective Date
1001	Babu	Illinois	California	15-JAN-2003

Advantages:
- This does not increase the size of the table, since new information is updated.
- This allows us to keep some part of history.
Disadvantages:
- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.

When to use Type 3:
Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

Different Date Formats with Convertion

SELECT CONVERT(VARCHAR(20),GETDATE(),100) -- Jul  2 2012  6:34PM

SELECT CONVERT(VARCHAR(20),GETDATE(),101) -- 07/02/2012

SELECT CONVERT(VARCHAR(20),GETDATE(),102) -- 2012.07.02

SELECT CONVERT(VARCHAR(20),GETDATE(),103) -- 02/07/2012

SELECT CONVERT(VARCHAR(20),GETDATE(),104) -- 02.07.2012

SELECT CONVERT(VARCHAR(20),GETDATE(),105) -- 02-07-2012

SELECT CONVERT(VARCHAR(20),GETDATE(),106) -- 02 Jul 2012

SELECT CONVERT(VARCHAR(20),GETDATE(),107) -- Jul 02, 2012

SELECT CONVERT(VARCHAR(20),GETDATE(),108) -- 18:34:54

SELECT CONVERT(VARCHAR(20),GETDATE(),109) -- Jul  2 2012  6:34:54

SELECT CONVERT(VARCHAR(20),GETDATE(),110) -- 07-02-2012

SELECT CONVERT(VARCHAR(20),GETDATE(),111) -- 2012/07/02

SELECT CONVERT(VARCHAR(20),GETDATE(),112) -- 20120702

SELECT CONVERT(VARCHAR(20),GETDATE(),113) -- 02 Jul 2012 18:34:54

SELECT CONVERT(VARCHAR(20),GETDATE(),114) -- 18:34:54:707

SELECT CONVERT(VARCHAR(20),GETDATE(),120) -- 2012-07-10 06:45:14

SELECT CONVERT(VARCHAR(20),GETDATE(),121) -- 2012-07-10 06:45:14.

SELECT CONVERT(VARCHAR(20),GETDATE(),126) -- 2012-07-10T06:45:14.

SELECT CONVERT(VARCHAR(20),GETDATE(),127) -- 2012-07-10T06:45:14.

SELECT CONVERT(VARCHAR(20),GETDATE(),130) -- 21 ????? 1433  6:45:

SELECT CONVERT(VARCHAR(20),GETDATE(),131) -- 21/08/1433  6:45:14:

Performing Control Flow task in SSIS Package

Steps to create Control Flow in SSIS package


Below is an example design for control flow

Get a SQL task and name it as 'SQL Insert Audit' to insert audit values and update the script in 'SQL Statement' field as given below:

exec spu_insert_audit ?,?, ?,?,?,? OUT

and go to the Parameter Mapping option and add the variables as shown below:

next, get a SQL task and name it as 'SQL Drop All FK' and update the script in 'SQL Statement' field as given below:

exec [dbo].[sp_ExecDropAndRebuildSql]  '','','drop','r' 

now get an 'Execute Package Task' and name it as 'EPT package_name'. In that, go to Package option and select the Location field as 'File System'. In Connection field, select the package name. See below:

now go to the variable option and create variables as shown below:

here the value path for the variable name 'sPkgFolder' is the place where the packages saved.

next we can see the package_name.dtsx created in the Connection Managers area. Select the package name and press F4 to go to the property window.

In Property window, go to the Expression field and select the property as 'ConnectionString' and expression as '@[User::sPkgFolder]+ @[User::sPkgFileNameStaging]'.  Drag the variables from the available list and form the script. Click the Evaluate Expression' button to confirm the expression. See below screen:

do the same step to connect all the packages in control flow as showed in the example design of control flow package in top.

Finally need to get a SQL task to rebuild the Foreign Key. Get a SQL task and name it as 'SQL Rebuild All FK' and update the script in 'SQL Statement' field as given below:

exec [dbo].[sp_ExecDropAndRebuildSql]  '','','create','F'

Performing Audit Task Summary in SSIS Package

Step to create Audit Task Summary package in SSIS


Get DFT Task and name it as "DFT_Load DBName_AuditTaskSummary".

Get into the DFT task and Design package as given below:

Now get into the OLE_Source task and write the SQL command text as given below to get the audit data from AuditTask table.

SELECT GETDATE() AS SummaryDate
              ,MAX(AuditTaskId) AS maxAuditTaskId
              ,MAX(AuditId) AS MaxAuditId
              ,PackageName
             ,TableName
 FROM dbo.AuditTask
 GROUP BY PackageName , TableName


Next, go to lookup task and name it as 'LKP AuditTaskSummary and set as "Redirect rows to no match output" to handle rows with no matching entries.
Go to 'Connection' option and write the below query in "Use results of an SQL query:" filed.


SELECT TableName
              ,PackageName
  FROM dbo.AuditTaskSummary


Next go to the 'Columns' option and map the cloumns as shown below:

From Lookup task, connect the Lookup Match Output to 'OLE DB Command' task. In that task, go the 'Component Properties' section and write the below query in the SqlCommand field.

Update dbo.AuditTaskSummary 
 Set SummaryDate = getDate(),
       MaxAuditTaskId = ?
where tableName = ? and PackageName = ?


And go to 'Column Mapping' section and map the columns as shown below. Check the exact data type match before map it.

Next, from the Lookup task, connect the Lookup No Match Output to 'OLE Destination named as 'OLE Dest_AuditTaskSummary' through Row Count task. Select the 'dbo.AuditTaskSummary' table and map the columns accordingly.

Performing Audit Function in Event Handlers in SSIS Package

Steps and scripts to create Audit function in Event Handlers tab in a SSIS package.

Create variables in package for declared values

--In the Event Handlers tab – do the below steps

Select "OnTaskFailed" - get Script Task and name it as 'SRC SetOnTaskFailed'– in the ReadWriteVariables option – select "User::Status" and this User::Status should be created in variable as "Status" and the value as "Completed".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

Dts.Variables["User::Status"].Value = "OnTaskFailed";

Dts.TaskResult = (int)ScriptResults.Success;

}

Select "OnError" - get Script Task and name it as 'SRC SetOnError'– in the ReadWriteVariables option – select "User::Status" and this User::Status should be created in variable as "Status" and the value as "Completed".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

Dts.Variables["User::Status"].Value = "OnError";

Dts.TaskResult = (int)ScriptResults.Success;

}

From the SRC SetOnError – get a line to connect an Exe SQL task and name it as 'SQL Recreate PK_Index_of_Target'.

In connector – select the Evalution Operation as "Constraint"

and Value as "Success".

Also select the Multiple Constraints with "OR" option.

And connect line to – "SQL Recreate PK_Index_of_Target" (Exe SQL Task)

In which, do as below:

--write the below script to insert values in Audit

EXEC [dbo].[sp_ExecDropAndRebuildSql] ?, ?, 'create', 'p,i'

And go the Parameter Mapping option and select as below

variableName      Direction    DataType  ParameterName  ParameterSize

User::SchemaName  Input      Varchar          0                   -1

User::TableName     Input      Varchar          1                   -1

Select "OnPreExecute" – get Script Task - In the ReadOnlyVariables option – select "System::SourceName,Uder::AuditId,System::PackageName" and in the ReadWriteVariables option – select "User::IsPackageLevel,User::IsParentValue" and this User::IsPackageLevel,User::IsParentValue and UserAuditId should be created in variable as "IsPackageLevel" with value as "False","IsParentValue" with value as "False" and "AuditId" with value as "-1".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

if (Dts.Variables["System::SourceName"].Value.Equals(Dts.Variables["System::PackageName"].Value))

{

Dts.Variables["User::IsPackageLevel"].Value = true;

if ((int)Dts.Variables["User::AuditId"].Value == -1)

{

Dts.Variables["User::IsParentValue"].Value = false;

}

else

{

Dts.Variables["User::IsParentValue"].Value = true;

}

}

else

{

Dts.Variables["User::IsPackageLevel"].Value = false;

}

Dts.TaskResult = (int)ScriptResults.Success;

}

From the SRC Intial – get two line to connect two Exe SQL task

"SQL Insert [DBName]_Audit (AND) SQL Insert [DBName]_AuditTask"

In connector I – select the Evalution Operation as "Expression and Constraint"

and Value as "Success" and Expression as "@IsPackageLevel && !@IsParentValue".

Also select the Multiple Constraints with "AND" option.

And connect line to – "SQL Insert Imports_Audit" (Exe SQL Task)

In which, do as below:

--write the below script to insert values in Audit

exec spu_insert_audit ?,?, ?,?,?,? OUT

And go the Parameter Mapping option and select as below

variable Name            Direction   DataType  ParameterName  ParameterSize

User::AuditId              Output       Long             5                      -1

System::StartTime       Input         Date             0                      -1

System::Versionbuild   Input          Long             1                     -1

System::UserName       Input        Varchar          2                      -1

System::PackageName Input         Varchar          3                      -1

System::MachineName Input         Varchar          4                      -1

In connector II – select the Evalution Operation as "Expression and Constraint"

and Value as "Success" and Expression as

"!(SUBSTRING(@[System::SourceName],1,4)=="FELC") && ! (SUBSTRING(@[System::SourceName],1,3)=="SRC") && !@IsPackageLevel".

Also select the Multiple Constraints with "AND" option.

And connect line to – "SQL Update Imports_AuditTask" (Exe SQL Task)

In which, do as below:

--write the below script to insert values in AuditTask

exec spu_insert_audit_task ?,?,?,?,?,?,?,?,? OUT

And go the Parameter Mapping option and select as below

variable Name             Direction   DataType   ParameterName   ParameterSize

User::AuditId                Input         Long              0                     -1

User::AuditTaskId          Output       Long             8                      -1

System::StartTime         Input         Date              1                     -1

System::Versionbuild     Input         Long              2                      -1

System::UserName        Input        Varchar           3                      -1

System::PackageName   Input        Varchar           4                      -1

System::MachineName    Input       Varchar           5                      -1

System::SourceName      Input       Varchar           6                       -1

User::TableName           Input        Varchar          7                       -1

Select "OnPostExecute" – get Script Task - In the ReadOnlyVariable option – select "System::SourceName,System::PackageName" and in the ReadWriteVariables option – select "User::IsPackageLevel" and this User::IsPackageLevel should be created in variable as "IsPackageLevel" and the value as "False".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

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;

}

From the SRC IsPackageLevel – get two line to connect two Exe SQL task

"SQL Update [DBName]_Audit (AND) SQL Update [DBName]_AuditTask"

In connector I – select the Evalution Operation as "Expression and Constraint"

and Value as "Success" and Expression as "@IsPackageLevel && !@IsParentValue".

Also select the Multiple Constraints with "AND" option.

And connect line to – "SQL Update Imports_Audit" (Exe SQL Task)

In which, do as below:

--write the below script to update values in Audit

UPDATE [dbo].[Audit]

SET [Status] = 'Completed'

,[EndTime] = GETDATE()

Where AuditId= -1

GO

Go to Expression – select the "SqlStatementSource" option and write below query:

"UPDATE [dbo].[Audit]
SET [Status] = '"+ @[User::Status] +"'
,[EndTime] = GETDATE()
Where AuditId= "+ (DT_WSTR, 20) (@[User::AuditId]) +"
GO
"

In connector II – select the Evalution Operation as "Expression and Constraint"

and Value as "Success" and Expression as "!@IsPackageLevel".

Also select the Multiple Constraints with "AND" option.

And connect line to – "SQL Update Imports_AuditTask" (Exe SQL Task)

In which, do as below:

--write the below script to update values in AuditTask

UPDATE [dbo].[AuditTask]

SET [Status] = 'Completed'

,[EndTime] = GETDATE()

,[RowsAffected]= 0

,[LastModDate]=''

Where AuditTaskId= '0'

GO

Go to Expression – select the "SqlStatementSource" option and write below query:

"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"

==================================================

To Create Audit function in Control flow

--In the Event Handlers tab – do the below steps

Select "OnTaskFailed" - get Script Task – in the ReadWriteVariables option – select "User::Status" and this User::Status should be created in variable as "Status" and the value as "Completed".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

Dts.Variables["User::Status"].Value = "OnTaskFailed";

Dts.TaskResult = (int)ScriptResults.Success;

}

Select "OnError" - get Script Task – in the ReadWriteVariables option – select "User::Status" and this User::Status should be created in variable as "Status" and the value as "Completed".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

Dts.Variables["User::Status"].Value = "OnError";

Dts.TaskResult = (int)ScriptResults.Success;

}

Select "OnPostExecute" – get Script Task - In the ReadOnlyVariable option – select "System::SourceName,System::PackageName" and in the ReadWriteVariables option – select "User::IsPackageLevel" and this User::IsPackageLevel should be created in variable as "IsPackageLevel" and the value as "False".

And go to Edit Script option – write the below query

public void Main()

{

// TODO: Add your code here

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;

}

From the SRC IsPackageLevel – get a line to connect an Exe SQL task

"SQL Update [DB Name]_Audit "

In connector – select the Evalution Operation as "Expression"

and Value as "Success" and Expression as "@IsPackageLevel || @Status!="Completed"".

Also select the Multiple Constraints with "OR" option.

And connect line to – "SQL Update [DB Name]_Audit " (Exe SQL Task)

In which, do as below:

--write the below script to update values in Audit

UPDATE [dbo].[Audit]

SET [Status] = 'Completed'

,[EndTime] = GETDATE()

Where AuditId= 0

GO

Go to Expression – select the "SqlStatementSource" option and write below query:

"UPDATE [dbo].[Audit]
SET [Status] = '"+ @[User::Status] +"'
,[EndTime] = GETDATE()
Where AuditId= "+ (DT_WSTR, 20) (@[User::AuditId]) +"
GO
"