How to remove duplicates rows in SSIS data flow task from a Flat file source or Excel source, Rawsource

Hi , Using a simple trick you can easily eliminate duplicate rows when using Flat file or Excel source or Raw source or any where in the Data Flow task























Simple and easy trick you can using any where in the Data Flow task

Thank you

SQLJUNKIESHARE

How to get the Time in HH:MM AM PM format IN SQL Server

How to get time in AM PM format in SQL Server

How to get time in 12 hour format in SQL Server






SELECT CONVERT(VARCHAR,GETDATE(),100) as ‘Default Date format’

SELECT RIGHT(‘0’+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),8)),7) as ’12 Hour Time in AMPM’

SELECT REPLACE(REPLACE(RIGHT(‘0’+LTRIM(RIGHT(CONVERT(varchar,getdate(),100),7)),7),’AM’,’ AM’),’PM’,’ PM’)

as ’12 Hour Time in AM PM’

Len() and Datalength()

In this post we will go through two scalar functions

Len() is one of the string functions

Datalength is one of the Data type functions

so what does Len() do , it returns(int) the number of characters of the specified string expression, excluding trailing blanks.

excluding trailing blanks is an important note to remember when using Len() function

 

 

 

— It can also be used in select, where clause

Declare

@string varchar(100)

set @string =‘sqljunkieshare’

select LEN(@string)

 select * from dept

where LEN(dept_id)> 5

 

Datalength() is data type function (size of the data)

It returns the number of bytes used to represent any expression.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now you have a clear idea of what does LEN() and DATALENTH()  scalar functions do

Set Operators : Except and Intersect -Tsql

SQL Server provides the following set operators. Set operators combine results from two or more queries into a single result set

1) Intersect and Except

2) Union

Today in this post we will learn Intersect and Except set operators

Returns distinct values by comparing the results of two queries.

EXCEPT returns any distinct values from the left query that are not also found on the right query.

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

  • The number and the order of the columns must be the same in all queries.
  • The data types must be compatible.

 

Lets take a simple example and you will better understand the set operators

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

create table #newproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #newproducts(ProductID,name,size,price)

values(1,’productx’,’small’,20),
(2,’producty’,’small’,40),
(3,’productz’,’large’,50),
(1,’productxy’,’small’,60)
create table #oldproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #oldproducts(ProductID,name,size,price)

values(4,’productq’,’small’,20),
(5,’productk’,’small’,40),
(6,’productz’,’large’,50),
(1,’productxy’,’small’,60),
(1,’productzy’,’small’,80)
select * from #newproducts

select * from #oldproducts
;with oldonly as
(
select * from #oldproducts

except

select * from #newproducts

),

newonly as (select * from #newproducts

except

select * from #oldproducts)
select o.ProductID,o.name as oldname, n.name as newname

from oldonly o inner join newonly n on o.ProductID = n.ProductID

where o.name <> n.name

System Functions – $partition

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

Syntax

[ database_name. ] $PARTITION.partition_function_name(expression)

It returns a Partition number of column value when mapped to a partition function

this will be useful if you want find the rows in a particular partition

so lets explain this with a simple example

 

create partition function

 

 

 

 

 

 

 

 

 

 

 

now lets create a partitioned table

 

 

 

 

 

insert some sample data

now lets see some examples on $partition function

SQL server 2012 ,Introduction to Reporting Services 2012 – Chart Series: Areas

Data Visualization plays an important role in the BI, as we know the Reporting services is one of the popular BI reporting tool in the Microsoft BI stack

In this I will demonstrate how to use charts in SQL server reporting services , specially we are going to focus on Area charts in this post

The most important thing in using the different charts in reporting services is that you have to know when to use right type of charts, as same information can be represented with  different visual elements so all it matters is using the right visual element in order to clearly expose the data characteristics to the business users

Area charts can be used for  Time series relationships and Distribution relationships

Time series relationships : A series of data points taken at equal intervals of time , this is the most used BI chat types, eg : sales over certain period of time

Distribution relationships: When a set of values is displayed across their entire range, this is especially used for measuring process efficiency, like no days, products complete

now you learnt little bit about charts we will see different types of Area charts

Area, Smooth Area, Stacked Area, 100% Stacked Area, 3-D Area, 3-D Smooth Area, 3-D Stacked Area, 3-D 100% Stacked Area

This is the preview of modified report

Customizing report using report properties