SQL 31

SQL Script to drop and re-create all foreign keys in SQL server

2014-02-21T15:39:00.001+00:00

I had to load some database with fresh data every few days for testing purpose and some reporting purpose. I have a SSIS package to load the data from source database but one issue I faced is I could not truncate the tables because of foreign keys. So Initially I started with using DELETE, but which was taking some time. So to improve the performance and get rid of all foreign key before start of the load and then re-create them after load is finish, I have written one interesting script as below. This script give DROP and CREATE statement for all foreign key which I was running in my package.

WITH RefColumns AS

(

SELECT

C.referenced_object_id AS [object_id],

C.parent_object_id,

STUFF((SELECT ', ' + QUOTENAME(B.name)

FROM sys.foreign_key_columns A

JOIN sys.columns B ON B.[object_id] = A.referenced_object_id AND B.column_id = A.referenced_column_id

WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id

FOR XML PATH('')), 1, 2, '') AS ColumnNames

FROM sys.foreign_key_columns C

GROUP BY C.referenced_object_id, C.parent_object_id

)

,ParentColumns AS

(

SELECT

C.parent_object_id AS [object_id],

C.referenced_object_id,

STUFF((SELECT ', ' + QUOTENAME(B.name)

FROM sys.foreign_key_columns A

JOIN sys.columns B ON B.[object_id] = A.parent_object_id AND B.column_id = A.parent_column_id

WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id

FOR XML PATH('')), 1, 2, '') AS ColumnNames

FROM sys.foreign_key_columns C

GROUP BY C.parent_object_id, C.referenced_object_id

)

SELECT

'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' DROP CONSTRAINT' + ' ' + QUOTENAME(FK.name) AS [DropFKScript],

'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' WITH CHECK ADD CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +

'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +

'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' + QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)

AS [CreateFKScript]

FROM sys.foreign_keys FK

JOIN sys.tables PT ON PT.[object_id] = FK.parent_object_id

JOIN ParentColumns AS PC ON PC.[object_id] = FK.parent_object_id AND PC.referenced_object_id = FK.referenced_object_id

JOIN sys.tables RT ON RT.[object_id] = FK.referenced_object_id

JOIN RefColumns AS RC ON RC.[object_id] = FK.referenced_object_id AND RC.parent_object_id = FK.parent_object_id

WHERE PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')

AND RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')

ORDER BY PT.name

If anyone find any error or think this can be improved then please do post your comment.

Compare data size definition and actual data for string field

2014-01-17T15:06:00.000+00:00

Most of the time we import data from file with default field length of 255. But once it is imported you might want to set it to correct size because of many reasons such as

For Constraint and keys.
Indexing strategy implementation.
Improve string comparison.
Maintaining data base field sizing standards

and more many other reason.

To do this we have to get the max length of current data and the compare it with database definition. We might have to do this for any existing table.

I have written one script which give the comparison of field size defined in table and actual data size. You can use this comparison and write script to make the necessary correction.

You can also use this script with some modification to generate ALTER script if you have defined rule for field size.

DECLARE @query VARCHAR(MAX)

DECLARE @tableName VARCHAR(255) = '[dbo].[SomeTable]'

SELECT @query = 'SELECT ' + SUBSTRING(A, 4, LEN(A)) + ' FROM ' + @tableName

FROM

(

SELECT

(

SELECT ' , MAX(LEN(['+ name +'])) AS ['+ name +']'

FROM sys.columns

WHERE [object_id] = OBJECT_ID(@tableName)

AND TYPE_NAME(system_type_id) IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR')

FOR XML PATH ('')

) AS A

) AS B

SELECT @query = ' SELECT A.ColumnName, UPPER(TYPE_NAME(B.system_type_id)) AS DataType, CASE WHEN TYPE_NAME(B.system_type_id) IN (''NCHAR'', ''NVARCHAR'') THEN B.max_length/2 ELSE B.max_length END AS MaxSizeByDefinition, A.MaxSizeByActualData

--Add here your script for ALTER query

FROM ( ' + @query + ' ) AS SourceTable UNPIVOT (MaxSizeByActualData FOR ColumnName IN ( ' + SUBSTRING(A, 4, LEN(A)) + ' )) AS A

INNER JOIN sys.columns AS B ON B.name = A.ColumnName AND B.[object_id] = OBJECT_ID(''' + @tableName + ''') ORDER BY B.column_id'

FROM

(

SELECT

(

SELECT ' , ['+ name + ']' FROM sys.columns C WHERE [object_id] = OBJECT_ID(@tableName) AND TYPE_NAME(system_type_id) IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR') FOR XML PATH ('')

) AS A

) AS B

EXECUTE(@query)

Create and Populate Date Dimension for Data Warehouse

2014-01-17T14:47:00.002+00:00

Recently I have been working on new data warehouse project using SSAS and SSIS. And I had to populate the Date dimension table, with granularity at individual day with various other values like Day of Month, Day of Week, Day Of Quarter, Day Name, Day of Week, Day of Week in month, First Day of Month, First Day of Quarter etc.

Quickly I did search internet but could not find the script which reasonably satisfy my requirement. So I have decided write my script as listed below.

This script will populate Date dimension for required as per UK dates like Holidays, Monday week start day etc. If you need to populate it for USA or other country then you have to change few things like Holiday Listing, FullDate, Week Start Day etc.

I found one issue while running this script while running on different server. This was related to First Day of Week. You can check first day of week using @@DATEFIRST. If it returns 1 then this script will work fine, but if it is other then 1 then you have to change the script calculation. To get away with this problem I have statement at beginning of this script to set first day of week to 1 using SET DATEFIRST 1.

This scripts populated below fields for Date dimension table.
DateKey -- Integer date key is created as YYYYMMDD
[Date] -- Actual date value

Day Of Month -- Day number of month
Day Of Month With Suffix -- Apply suffix as 1st, 2nd ,3rd etc

Day Of Quarter -- Day number of quarter
Day Of Quarter With Suffix -- Apply suffix as 1st, 2nd ,3rd etc
Month Of Quarter -- Month number of quarter

Day Name -- Name of the day, Sunday, Monday
Day Of Week In Month --1st Monday, 2nd Monday etc in Month
Week Of Month -- Week Number of Month
Week Of Quarter -- Week Number of the Quarter
Month Name -- January, February etc
Month Year -- Jan-2014, Feb-2014 etc
MMYYYY
First Day Of Month -- Date for first day of month
Last Day Of Month -- Date for last day of month
First Day Of Quarter -- Date for first day of quarter
Last Day Of Quarter -- Date for last day of quarter

Full Date -- Date in dd-MM-yyyy format
Day Of Week -- First Day Monday=1 and Sunday=7

Day Of Calender Year -- Day number in calender year
Week Of Calender Year -- Week Number of the calender Year
Calender Month -- Number of the Month 1 to 12
Calender Quarter -- Calender quarter number Jan-Mar = 1, Apr-Jun=2, Jul-Sep = 3 and Oct-Dec = 4
Calender Year -- Year value of Date stored as YYYY

Day Of Financial Year -- Day number in financial year
Week Of Financial Year -- Week Number of the financial Year
Financial Month -- Month number in financial year being Apr=1 and Mar=12
Financial Quarter -- Calender quarter number Apr-Jun = 1, Jul-Sep = 2, Oct-Dec = 3 and Jan-Mar = 4
FinancialYear -- Year value of Date stored as YYYY/YYYY

IsWeekday -- 0=Week End ,1=Week Day
IsHoliday -- Flag 1=National Holiday, 0=No National Holiday
HolidayName -- Name of Holiday in UK

Please find the compete script as below. Please let me know if you need any help or if you feel I have done something wrong in this script.

SET DATEFIRST 1

DECLARE @Holiday TABLE

(

[Date] DATE,

HolidayName VARCHAR(50)

)

--Data Source https://www.gov.uk/bank-holidays (as on 2014-01-07), Only England and Wales holidays are considered

INSERT INTO @Holiday([Date], HolidayName)

VALUES('2012-01-02', 'New Year''s Day(substitute day)'),

('2012-04-06', 'Good Friday'),

('2012-04-09', 'Easter Monday'),

('2012-05-07', 'Early May bank holiday'),

('2012-06-04', 'Spring bank holiday (substitute day)'),

('2012-06-05', 'Queen''s Diamond Jubilee (extra bank holiday)'),

('2012-08-27', 'Summer bank holiday'),

('2012-12-25', 'Christmas Day'),

('2012-12-26', 'Boxing Day'),

('2013-01-01', 'New Year''s Day'),

('2013-03-29', 'Good Friday'),

('2013-04-01', 'Easter Monday'),

('2013-05-06', 'Early May bank holiday'),

('2013-05-27', 'Spring bank holiday'),

('2013-08-26', 'Summer bank holiday'),

('2013-12-25', 'Christmas Day'),

('2013-12-26', 'Boxing Day'),

('2014-01-01', 'New Year''s Day'),

('2014-04-18', 'Good Friday'),

('2014-04-21', 'Easter Monday'),

('2014-05-05', 'Early May bank holiday'),

('2014-05-26', 'Spring bank holiday'),

('2014-08-25', 'Summer bank holiday'),

('2014-12-25', 'Christmas Day'),

('2014-12-26', 'Boxing Day'),

('2015-01-01', 'New Year''s Day'),

('2015-04-03', 'Good Friday'),

('2015-04-06', 'Easter Monday'),

('2015-05-04', 'Early May bank holiday'),

('2015-05-25', 'Spring bank holiday'),

('2015-08-31', 'Summer bank holiday'),

('2015-12-25', 'Christmas Day'),

('2015-12-28', 'Boxing Day (substitute day)')

DECLARE @DimDate TABLE

(

DateKey INT PRIMARY KEY, -- Integer date key is created as YYYYMMDD

[Date] DATE, -- Actual date value

[DayOfMonth] VARCHAR(2), -- Day number of month

DayOfMonthWithSuffix VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc

DayOfQuarter VARCHAR(2), -- Day number of quarter

DayOfQuarterWithSuffix VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc

MonthOfQuarter VARCHAR(1), -- Month number of quarter

[DayName] VARCHAR(9), -- Name of the day, Sunday, Monday

DayOfWeekInMonth VARCHAR(15), --1st Monday, 2nd Monday etc in Month

WeekOfMonth VARCHAR(1), -- Week Number of Month

WeekOfQuarter VARCHAR(2), -- Week Number of the Quarter

[MonthName] VARCHAR(9), -- January, February etc

MonthYear CHAR(10), -- Jan-2014, Feb-2014 etc

MMYYYY CHAR(6),

FirstDayOfMonth DATE, -- Date for first day of month

LastDayOfMonth DATE, -- Date for last day of month

FirstDayOfQuarter DATE, -- Date for first day of quarter

LastDayOfQuarter DATE, -- Date for last day of quarter

FullDate CHAR(10), -- Date in dd-MM-yyyy format

[DayOfWeek] CHAR(1), -- First Day Monday=1 and Sunday=7

--Calender

DayOfCalenderYear VARCHAR(3), -- Day number in calender year

WeekOfCalenderYear VARCHAR(2), -- Week Number of the calender Year

CalenderMonth VARCHAR(2), -- Number of the Month 1 to 12

CalenderQuarter CHAR(1), -- Calender quarter number Jan-Mar = 1, Apr-Jun=2, Jul-Sep = 3 and Oct-Dec = 4

CalenderYear CHAR(4), -- Year value of Date stored as YYYY

--Financial

DayOfFinancialYear VARCHAR(3), -- Day number in financial year

WeekOfFinancialYear VARCHAR(2), -- Week Number of the financial Year

FinancialMonth VARCHAR(2), -- Month number in financial year being Apr=1 and Mar=12

FinancialQuarter CHAR(1), -- Calender quarter number Apr-Jun = 1, Jul-Sep = 2, Oct-Dec = 3 and Jan-Mar = 4

FinancialYear CHAR(9), -- Year value of Date stored as YYYY/YYYY

[IsWeekday] BIT, -- 0=Week End ,1=Week Day

IsHoliday BIT, -- Flag 1=National Holiday, 0=No National Holiday

HolidayName VARCHAR(50) -- Name of Holiday in UK

)

DECLARE @year INT,

@startDate DATE,

@endDate DATE,

@currentDate DATE,

@DateKey INT,

@Date DATETIME,

@DayOfMonth VARCHAR(2),

@DayOfMonthWithSuffix VARCHAR(4),

@DayOfQuarter VARCHAR(2),

@DayOfQuarterWithSuffix VARCHAR(4),

@MonthOfQuarter VARCHAR(1),

@DayName VARCHAR(9),

@DayOfWeekInMonth VARCHAR(15),

@WeekOfMonth VARCHAR(1),

@WeekOfQuarter VARCHAR(2),

@MonthName VARCHAR(9),

@MonthYear CHAR(10),

@MMYYYY CHAR(6),

@FirstDayOfMonth DATE,

@LastDayOfMonth DATE,

@FirstDayOfQuarter DATE,

@LastDayOfQuarter DATE,

@FullDate CHAR(10),

@DayOfWeek CHAR(1),

@DayOfCalenderYear VARCHAR(3),

@WeekOfCalenderYear VARCHAR(2),

@CalenderMonth VARCHAR(2),

@CalenderQuarter CHAR(1),

@CalenderYear CHAR(4),

@DayOfFinancialYear VARCHAR(3),

@WeekOfFinancialYear VARCHAR(2),

@FinancialMonth VARCHAR(2),

@FinancialQuarter CHAR(1),

@FinancialYear CHAR(9),

@IsWeekday BIT,

@IsHoliday BIT,

@HolidayName VARCHAR(50)

SET @startDate = '2014-01-01'

SET @endDate = '2014-12-31'

SET @currentDate = @startDate

WHILE @currentDate<=@endDate

BEGIN

SET @DateKey = CAST(CONVERT(VARCHAR, @currentDate, 112) AS INT)

SET @Date = @currentDate

SET @DayOfMonth = CASE WHEN DAY(@currentDate) <= 9 THEN '0' + CAST(DAY(@currentDate) AS VARCHAR) ELSE CAST(DAY(@currentDate) AS VARCHAR) END

SET @DayOfMonthWithSuffix = CASE WHEN DAY(@currentDate) IN (1, 21, 31) THEN CAST(DAY(@currentDate) AS VARCHAR) + 'st'

WHEN DAY(@currentDate) IN (2, 22) THEN CAST(DAY(@currentDate) AS VARCHAR) + 'nd'

WHEN DAY(@currentDate) IN (3, 23) THEN CAST(DAY(@currentDate) AS VARCHAR) + 'rd'

ELSE CAST(DAY(@currentDate) AS VARCHAR) + 'th'

END

SET @DayOfQuarter = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN DATEDIFF(D, CAST(YEAR(@currentDate) AS VARCHAR) + '-01-01', @currentDate) + 1

WHEN MONTH(@currentDate) IN (4, 5, 6) THEN DATEDIFF(D, CAST(YEAR(@currentDate) AS VARCHAR) + '-04-01', @currentDate) + 1

WHEN MONTH(@currentDate) IN (7, 8, 9) THEN DATEDIFF(D, CAST(YEAR(@currentDate) AS VARCHAR) + '-07-01', @currentDate) + 1

WHEN MONTH(@currentDate) IN (10, 11, 12) THEN DATEDIFF(D, CAST(YEAR(@currentDate) AS VARCHAR) + '-10-01', @currentDate) + 1

END

SET @DayOfQuarterWithSuffix = CASE WHEN @DayOfQuarter IN (1, 21, 31, 41, 51, 61, 71, 81, 91) THEN CAST(@DayOfQuarter AS VARCHAR) + 'st'

WHEN @DayOfQuarter IN (2, 22, 32, 42, 52, 62, 72, 82, 92) THEN CAST(@DayOfQuarter AS VARCHAR) + 'nd'

WHEN @DayOfQuarter IN (3, 23, 33, 43, 53, 63, 73, 83, 93) THEN CAST(@DayOfQuarter AS VARCHAR) + 'rd'

ELSE CAST(@DayOfQuarter AS VARCHAR) + 'th'

END

SET @DayOfQuarter = CASE WHEN CAST(@DayOfQuarter AS INT) <= 9 THEN '0' + CAST(CAST(@DayOfQuarter AS INT) AS VARCHAR) ELSE @DayOfQuarter END

SET @MonthOfQuarter = CASE WHEN MONTH(@currentDate)%3 = 0 THEN 3 ELSE MONTH(@currentDate)%3 END

SET @DayName = DATENAME(DW, @CurrentDate)

DECLARE @FirstDayNameOfTheMonth VARCHAR(9),

@DayInWeekKey INT

SET @FirstDayOfMonth = CAST(YEAR(@currentDate) AS VARCHAR) + '-' + CAST(MONTH(@currentDate) AS VARCHAR) + '-01'

SET @FirstDayNameOfTheMonth = DATENAME(DW, @FirstDayNameOfTheMonth)

SET @DayInWeekKey = DATEPART(dw, @FirstDayOfMonth)

SET @WeekOfMonth = CASE WHEN DATENAME(DW, @FirstDayOfMonth) = 'Monday' THEN (DAY(@CurrentDate)-1)/7 + 1

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Tuesday' THEN (DAY(@CurrentDate))/7 + 1

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Wednesday' THEN (DAY(@CurrentDate)+1)/7 + 1

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Thursday' THEN (DAY(@CurrentDate)+2)/7 + 1

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Friday' THEN (DAY(@CurrentDate)+3)/7 + 1

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Saturday' THEN (DAY(@CurrentDate)+4)/7 + 1

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Sunday' THEN (DAY(@CurrentDate)+5)/7 + 1

END

SET @MonthName = DATENAME(MM, @CurrentDate)

SET @MonthYear = SUBSTRING(DATENAME(MM, @CurrentDate), 1, 3) + '-' + CAST(YEAR(@currentDate) AS VARCHAR)

SET @MMYYYY = SUBSTRING(CONVERT(VARCHAR, @currentDate, 112), 1, 6)

SET @LastDayOfMonth = DATEADD(D,-1, DATEADD(M, 1, @FirstDayOfMonth))

SET @FirstDayOfQuarter = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN CAST(YEAR(@currentDate) AS VARCHAR) + '-' + '01-01'

WHEN MONTH(@currentDate) IN (4, 5, 6) THEN CAST(YEAR(@currentDate) AS VARCHAR) + '-' + '04-01'

WHEN MONTH(@currentDate) IN (7, 8, 9) THEN CAST(YEAR(@currentDate) AS VARCHAR) + '-' + '07-01'

WHEN MONTH(@currentDate) IN (10, 11, 12) THEN CAST(YEAR(@currentDate) AS VARCHAR) + '-' + '10-01'

END

SET @LastDayOfQuarter = DATEADD(D, -1, DATEADD(MM, 3, @FirstDayOfQuarter))

SET @WeekOfQuarter = CASE WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Monday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate))/7 + 1

WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Tuesday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate)+1)/7 + 1

WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Wednesday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate)+2)/7 + 1

WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Thursday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate)+3)/7 + 1

WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Friday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate)+4)/7 + 1

WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Saturday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate)+5)/7 + 1

WHEN DATENAME(DW, @FirstDayOfQuarter) = 'Sunday' THEN (DATEDIFF(D, @FirstDayOfQuarter, @CurrentDate)+6)/7 + 1

END

SET @WeekOfQuarter = CASE WHEN CAST(@WeekOfQuarter AS INT) <= 9 THEN '0' + CAST(CAST(@WeekOfQuarter AS INT) AS VARCHAR) ELSE @WeekOfQuarter END

SET @FullDate = CONVERT(VARCHAR, @currentDate, 103)

SET @DayOfWeek = DATEPART(DW, @CurrentDate)

SET @DayOfWeekInMonth = CASE WHEN DATENAME(DW, @FirstDayOfMonth) = 'Monday'

THEN CASE

WHEN @WeekOfMonth = 1 THEN @WeekOfMonth + 'st ' + @dayName

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @dayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @dayName

ELSE @WeekOfMonth + 'th ' + @dayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Tuesday' AND @DayOfWeek < 2

THEN CASE

WHEN @WeekOfMonth-1 = 1 THEN CAST(@WeekOfMonth-1 AS VARCHAR) + 'st ' + @dayName

WHEN @WeekOfMonth-1 = 2 THEN CAST(@WeekOfMonth-1 AS VARCHAR) + 'nd ' + @dayName

WHEN @WeekOfMonth-1 = 3 THEN CAST(@WeekOfMonth-1 AS VARCHAR) + 'rd ' + @dayName

ELSE CAST(@WeekOfMonth-1 AS VARCHAR) + 'th ' + @dayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Tuesday' AND @DayOfWeek >= 2

THEN CASE

WHEN @WeekOfMonth = 1 THEN @WeekOfMonth + 'st ' + @dayName

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @dayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @dayName

ELSE @WeekOfMonth + 'th ' + @dayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Wednesday' AND @DayOfWeek < 3

THEN CASE

WHEN (CAST(@WeekOfMonth AS INT)-1) = 1 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'st ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 2 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'nd ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 3 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'rd ' + @DayName

ELSE CAST(@WeekOfMonth-1 AS VARCHAR) + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Wednesday' AND @DayOfWeek >= 3

THEN CASE

WHEN @WeekOfMonth = 1 THEN ISNULL(@WeekOfMonth + 'st ' + @DayName, 'ZZZ')

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @DayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @DayName

ELSE @WeekOfMonth + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Thursday' AND @DayOfWeek < 4

THEN CASE

WHEN (CAST(@WeekOfMonth AS INT)-1) = 1 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'st ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 2 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'nd ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 3 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'rd ' + @DayName

ELSE CAST(@WeekOfMonth-1 AS VARCHAR) + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Thursday' AND @DayOfWeek >= 4

THEN CASE

WHEN @WeekOfMonth = 1 THEN ISNULL(@WeekOfMonth + 'st ' + @DayName, 'ZZZ')

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @DayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @DayName

ELSE @WeekOfMonth + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Friday' AND @DayOfWeek < 5

THEN CASE

WHEN (CAST(@WeekOfMonth AS INT)-1) = 1 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'st ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 2 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'nd ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 3 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'rd ' + @DayName

ELSE CAST(@WeekOfMonth-1 AS VARCHAR) + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Friday' AND @DayOfWeek >= 5

THEN CASE

WHEN @WeekOfMonth = 1 THEN ISNULL(@WeekOfMonth + 'st ' + @DayName, 'ZZZ')

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @DayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @DayName

ELSE @WeekOfMonth + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Saturday' AND @DayOfWeek < 6

THEN CASE

WHEN (CAST(@WeekOfMonth AS INT)-1) = 1 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'st ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 2 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'nd ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 3 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'rd ' + @DayName

ELSE CAST(@WeekOfMonth-1 AS VARCHAR) + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Saturday' AND @DayOfWeek >= 6

THEN CASE

WHEN @WeekOfMonth = 1 THEN ISNULL(@WeekOfMonth + 'st ' + @DayName, 'ZZZ')

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @DayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @DayName

ELSE @WeekOfMonth + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Sunday' AND @DayOfWeek < 7

THEN CASE

WHEN (CAST(@WeekOfMonth AS INT)-1) = 1 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'st ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 2 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'nd ' + @DayName

WHEN CAST(@WeekOfMonth AS INT)-1 = 3 THEN CAST(CAST(@WeekOfMonth AS INT)-1 AS VARCHAR) + 'rd ' + @DayName

ELSE CAST(@WeekOfMonth-1 AS VARCHAR) + 'th ' + @DayName END

WHEN DATENAME(DW, @FirstDayOfMonth) = 'Sunday' AND @DayOfWeek >= 7

THEN CASE

WHEN @WeekOfMonth = 1 THEN ISNULL(@WeekOfMonth + 'st ' + @DayName, 'ZZZ')

WHEN @WeekOfMonth = 2 THEN @WeekOfMonth + 'nd ' + @DayName

WHEN @WeekOfMonth = 3 THEN @WeekOfMonth + 'rd ' + @DayName

ELSE @WeekOfMonth + 'th ' + @DayName END

END

SET @DayOfCalenderYear = DATEDIFF(D, CAST(YEAR(@currentDate) AS VARCHAR) + '-01-01', @currentDate) + 1

SET @DayOfCalenderYear = CASE WHEN CAST(@DayOfCalenderYear AS INT) <= 9 THEN '00' + CAST(CAST(@DayOfCalenderYear AS INT) AS VARCHAR)

WHEN CAST(@DayOfCalenderYear AS INT) <= 99 THEN '0' + CAST(CAST(@DayOfCalenderYear AS INT) AS VARCHAR)

ELSE @DayOfCalenderYear END

SET @WeekOfCalenderYear = DATEPART(wk, @CurrentDate)

SET @WeekOfCalenderYear = CASE WHEN CAST(@WeekOfCalenderYear AS INT) <= 9 THEN '0' + CAST(CAST(@WeekOfCalenderYear AS INT) AS VARCHAR) ELSE @WeekOfCalenderYear END

SET @CalenderMonth = MONTH(@currentDate)

SET @CalenderMonth = CASE WHEN CAST(@CalenderMonth AS INT) <= 9 THEN '0' + CAST(CAST(@CalenderMonth AS INT) AS VARCHAR) ELSE @CalenderMonth END

SET @CalenderQuarter = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN 1

WHEN MONTH(@currentDate) IN (4, 5, 6) THEN 2

WHEN MONTH(@currentDate) IN (7, 8, 9) THEN 3

WHEN MONTH(@currentDate) IN (10, 11, 12) THEN 4

END

SET @CalenderYear = YEAR(@currentDate)

SET @DayOfFinancialYear = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN DATEDIFF(D, CAST((YEAR(@currentDate)-1) AS VARCHAR) + '-04-01', @currentDate) + 1

ELSE DATEDIFF(D, CAST(YEAR(@currentDate) AS VARCHAR) + '-04-01', @currentDate) + 1

END

SET @DayOfFinancialYear = CASE WHEN CAST(@DayOfFinancialYear AS INT) <= 9 THEN '00' + CAST(CAST(@DayOfFinancialYear AS INT) AS VARCHAR)

WHEN CAST(@DayOfFinancialYear AS INT) <= 99 THEN '0' + CAST(CAST(@DayOfFinancialYear AS INT) AS VARCHAR)

ELSE @DayOfFinancialYear END

SET @WeekOfFinancialYear = CASE WHEN MONTH(@CurrentDate) <= 3 THEN (DATEPART(wk, CAST((CAST(YEAR(@CurrentDate)-1 AS CHAR) + '-12-31') AS DATE)) - DATEPART(wk, CAST((CAST(YEAR(@CurrentDate)-1 AS CHAR) + '-04-01') AS DATE)) + DATEPART(wk, @CurrentDate))

ELSE (DATEPART(wk, @CurrentDate) - DATEPART(wk, CAST((CAST(YEAR(@CurrentDate) AS CHAR) + '-04-01') AS DATE)) + 1) END

SET @WeekOfFinancialYear = CASE WHEN CAST(@WeekOfFinancialYear AS INT) <= 9 THEN '0' + CAST(CAST(@WeekOfFinancialYear AS INT) AS VARCHAR) ELSE @WeekOfFinancialYear END

SET @FinancialMonth = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN 9+MONTH(@currentDate) ELSE MONTH(@currentDate)-3 END

SET @FinancialMonth = CASE WHEN CAST(@FinancialMonth AS INT) <= 9 THEN '0' + CAST(CAST(@FinancialMonth AS INT) AS VARCHAR) ELSE @FinancialMonth END

SET @FinancialQuarter = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN 4

WHEN MONTH(@currentDate) IN (4, 5, 6) THEN 1

WHEN MONTH(@currentDate) IN (7, 8, 9) THEN 2

WHEN MONTH(@currentDate) IN (10, 11, 12) THEN 3

END

SET @FinancialYear = CASE WHEN MONTH(@currentDate) IN (1, 2, 3) THEN CAST((YEAR(@currentDate)-1) AS VARCHAR) + '/' + CAST((YEAR(@currentDate)) AS VARCHAR) ELSE CAST((YEAR(@currentDate)) AS VARCHAR) + '/' + CAST((YEAR(@currentDate)+1) AS VARCHAR) END

SET @IsWeekday = CASE WHEN @DayOfWeek IN (6, 7) THEN 0 ELSE 1 END

SET @IsHoliday = CASE WHEN EXISTS(SELECT * FROM @Holiday WHERE [Date] = @Date) THEN 1 ELSE 0 END

SET @HolidayName = CASE WHEN @IsHoliday = 1 THEN (SELECT HolidayName FROM @Holiday WHERE [Date] = @Date) ELSE '' END

INSERT INTO @DimDate

(

DateKey,

[Date],

[DayOfMonth],

DayOfMonthWithSuffix,

DayOfQuarter,

DayOfQuarterWithSuffix,

MonthOfQuarter,

[DayName],

DayOfWeekInMonth,

WeekOfMonth,

WeekOfQuarter,

[MonthName],

MonthYear,

MMYYYY,

FirstDayOfMonth,

LastDayOfMonth,

FirstDayOfQuarter,

LastDayOfQuarter,

FullDate,

[DayOfWeek],

DayOfCalenderYear,

WeekOfCalenderYear,

CalenderMonth,

CalenderQuarter,

CalenderYear,

DayOfFinancialYear,

WeekOfFinancialYear,

FinancialMonth,

FinancialQuarter,

FinancialYear,

[IsWeekday],

IsHoliday,

HolidayName

)

VALUES

(

@DateKey,

@Date,

@DayOfMonth,

@DayOfMonthWithSuffix,

@DayOfQuarter,

@DayOfQuarterWithSuffix,

@MonthOfQuarter,

@DayName,

@DayOfWeekInMonth,

@WeekOfMonth,

@WeekOfQuarter,

@MonthName,

@MonthYear,

@MMYYYY,

@FirstDayOfMonth,

@LastDayOfMonth,

@FirstDayOfQuarter,

@LastDayOfQuarter,

@FullDate,

@DayOfWeek,

@DayOfCalenderYear,

@WeekOfCalenderYear,

@CalenderMonth,

@CalenderQuarter,

@CalenderYear,

@DayOfFinancialYear,

@WeekOfFinancialYear,

@FinancialMonth,

@FinancialQuarter,

@FinancialYear,

@IsWeekday,

@IsHoliday,

@HolidayName

)

SET @currentDate = DATEADD(D, 1, @currentDate)

END

SELECT * FROM @DimDate

Measures, Attributes and Hierarchies

2013-12-30T10:47:00.001+00:00

Measures

A measure represents a data column which contains quantifiable data, usually numeric, that can be aggregated. A measure is generally mapped to a column in a fact table.

In SSAS measures are grouped by underlying fast tables, which called Measure Groups. Measure Groups are used to associate dimensions with measures.

Measure are selected while creating Cube, which can later be modified for various usage like its formatting, aggregate behaviour etc. Click here to see how to modify measure for formatting.

Attributes

Attribute represent one or more column in dimension table. Attribute is used to particular expect of the dimension. For example product colour represent colour attribute of the product dimension, which can later be used for slicing/dicing/selecting the cube values. Each dimension in cube contains one Key attribute, which is generally primary key in dimension table.

Click here to see how to define/update attribute for dimension.

Hierarchies

As name suggest Hierarchies is used to define hierarchy in dimension. For example Product and Product Category, National and International region etc. Hierarchy can be defined in Hierarchies table in cube design.

Click here to see how to add/delete user-defined Hierarchy.

Tool to view big data file

2013-12-16T16:32:00.001+00:00

I have to load big data files using SSIS or SQL server Import/Export wizard. Most of the time it works fine but if it fails and I have to view the data to debug then it is simply impossible to view the data because most of the text editor cannot open file which too big (size in multiple of GBs). So I have developed small tool which can help to read or view a big file which cannot be opened in normal text editor. This tool can also be use full if you are developing SSIS package to load big file and want to create small sample file from big file during development.

Click here to download the tool.

Features of the tools

- Load big file (no limit on size)
- View data page by page. Page size is configurable.
- Save page in different file.
- Go to any page in file.
- Displays line number across pages.

Limitations.
- It uses Temp directory to store data pages while opening file. So virtually it can occupy similar disk space as the original file.
- It can only read file line by line. So if lines are not separated by {CR}{LF} then it cannot do data paging.
- It can only work with ANSI data.

Read big data file | View big data file | Open big data file | tool to open big file | tool to read big data file | Preview big data file | Get sample data from big data file | Read specific data from big file | View specific data from big file

Dimension and Cube

2013-12-16T16:09:00.002+00:00

Dimension
A database dimension is a collection of attributes usually related objects, which can be used to provide elaborated information about fact data in cube(s). Typical example of attributes in a Employee dimension might be Employee name, Employee Designation, Employee Qualification, Joining Date etc. Dimensions are linked to one or more columns in one or more tables in a data source view. By default, these attributes are visible as attribute hierarchies and can be used to understand the fact data in a cube. Attributes can be organized into user-defined hierarchies that provide navigational paths to assist users when browsing the data in a cube.

Click here for step by step instruction to create dimension in using existing table in SSDT.
Click here for step by step instruction to create dimension in using Dimension wizard in SSDT.

Cube
A cube is a multidimensional object that contains information for analytical purposes; which contains dimensions and measures. Dimensions define the structure of the cube that is used to slice and dice over, and measures provide aggregated numerical values of interest to the end user. As a logical structure, a cube allows a client application to retrieve values, of measures, as if they were contained in cells in the cube; cells are defined for every possible summarized value. A cell, in the cube, is defined by the intersection of dimension members and contains the aggregated values of the measures at that specific intersection.

Click here for step by step instruction to create Cube in using Cube wizard in SSDT.

Data Source and Data Source View

2013-11-29T11:17:00.002+00:00

Data Source

A data source in Analysis Services specifies a direct connection to an external data source. In addition to physical location, a data source object specifies the connection string, data provider, credentials, and other properties that control connection behaviour.

Information provided by the data source object is used during the following operations:

Get schema information and other metadata used to generate data source views into a model.
Query or load data into a model during processing.
Run queries against multidimensional or data mining models that use ROLAP storage mode.
Read or write to remote partitions.
Connect to linked objects, as well as synchronize from target to source.
Perform write back operations that update fact table data stored in a relational database.

      When building a multidimensional model from the bottom up, you start by creating the data source object, and then use it to generate the next object, a data source view. A data source view is the data abstraction layer in the model. It is typically created after the data source object, using the schema of the source database as the basis. However, you can choose other ways to build a model, including starting with cubes and dimensions, and generating the schema that best supports your design.

       Regardless of how you build it, each model requires at least one data source object that specifies a connection to source data. You can create multiple data source objects in a single model to use data from different sources or vary connection properties for specific objects.

       Data source objects can be managed independently of other objects in your model. After you create a data source, you can change its properties later, and then pre-process the model to ensure the data is retrieved correctly.

Click here for step by step instruction to create data source.

Data Source View

         A data source view (DSV) is an abstraction of a relational data source that becomes the basis of the cubes and dimensions you create in a multidimensional project. The purpose of a DSV is to give you control over the data structures used in your project, and to work independently of the underlying data sources (for example, the ability to rename or concatenate columns without directly modifying the original data source).

        You can build multiple data source views in an Analysis Services project or database on one or more data sources, and construct each one to satisfy the requirements for a different solution.

Click here for step by step instruction to create data source view.

SSAS Tutorials : Multidimensional Modeling

2013-11-28T12:23:00.004+00:00

SSAS Multidimensional Modelling Tutorials

Data Source and Data Source View
Dimension and Cube
Measures, Attributes and Hierarchies
Advanced Attribute and Dimension Properties
Relationships Between Dimensions and Measure Groups
Calculations
Key Performance Indicators (KPIs)
Actions
Perspectives and Translations
Administrative Roles

SQL Server Analysis Services (SSAS) business intelligence (BI) semantic modelling options

2013-11-28T12:19:00.001+00:00

SQL Server Analysis Services (SSAS) provides three different options to create business intelligence semantic model. Namely those 3 options are tabular, multidimensional and PowerPivot.

Tabular model uses relational database modelling objects such as tables, views and relationships for modeling data, and the xVelocity in-memory analytics engine for storing and calculating data.
Multidimensional model uses OLAP modelling constructs such as cubes and dimensions and uses MOLAP, ROLAP, or HOLAP storage.
PowerPivot model is a self-service BI solution that lets business analysts build an analytical data model inside an Excel workbook using the PowerPivot for Excel add-in. PowerPivot also uses xVelocity in-memory analytics engine within Excel and on SharePoint.

Table below summarizes feature available for each modelling option.

	Multidimensional	Tabular	PowerPivot
Actions	Yes	No	No
Aggregations	Yes	No	No
Calculated Measures	Yes	Yes	Yes
Custom Assemblies	Yes	No	No
Custom Rollups	Yes	No	No
Distinct Count	Yes	Yes (via DAX)	Yes (via DAX)
Drillthrough	Yes	Yes	Yes (detail opens in separate worksheet)
Hierarchies	Yes	Yes	Yes
KPIs	Yes	Yes	Yes
Linked objects	Yes	No	Yes (linked tables)
Many-to-many relationships	Yes	No	No
Parent-child Hierarchies	Yes	Yes (via DAX)	Yes (via DAX)
Partitions	Yes	Yes	No
Perspectives	Yes	Yes	Yes
Semi-additive Measures	Yes	Yes	Yes
Translations	Yes	No	No
User-defined Hierarchies	Yes	Yes	Yes
Writeback	Yes	No	No

See below links to learn about each modelling option.

Tabular Modelling
Multidimensional Modelling

PowerPivot Modelling

How to setup database mirroring session in MS SQL server.

2013-03-20T16:18:00.001+00:00

Following is step by step guide for setting up database mirroring session. There are 2 major steps involved in setting up database mirroring setup.

Prepare mirror database.
Configure database mirroring session.

Now let's see both of these steps in detail.

Prepare mirror database.

Take full backup of the primary database. You can use existing full backup file if you have available one. Before taking full backup make sure that database recovery model is set to full. To check recovery model Right click on database - Click on property - Select Options Tab.
Restore full back from Step - 1. This restore must be done using WITH NORECOVERY option. And resored database name must be same as primary database.
Restore differential backup (if any) taken after full backup used to restore in step 2. Also restore log backup (if any) taken after full backup or differential backup. All these restore must use WITH NORECOVERY option.

Now your secodary database is ready to start. Now lets see how to configure actual mirroring session.

Configure databae mirroring session.

Open database mirroring setup wizard in SSMS. Right click on database - Select Tasks - Click on Mirror... OR Right click on databaes - Click on Property - Select Mirroring Tab

Open Databaes Mirroring Security Wizard by clicking on Configure Secutity... button.

Click on Next. To make things simple we will skip witness setup option. So select No on Include Witness Server page and click next. Deselect witness server instance and click next.

This will open Primary server Instance wizard. Enter end point name and Listener port (leave default for this example) and click on Next.

Click on connect to Secodary Serever. Provide authentication details and connect to secodary server. Leave other details as default and click on next.

Now your session is almost ready. Click on finish. Which popup below window. You can start mirroring immidiately or start later form main mirroing tab. See below screen.

You can also generate script before clicking on Ok on main tab. Scripit is use full for documentaion and future reference. So it is always advisable to generate script and save somewhere safe, probably in source control.

T-SQL reference for Database Mirroring

2013-03-20T15:16:00.001+00:00

How to manually failover to partner server?

There are 2 ways to manually failover to partner server in SQL server database mirroring session.
First is from primary server server and second is from secondary server. See below script for partner failover.

--1. Manual Failover from Primary server

ALTER DATABASE <Database_Name> SET PARTNER FAILOVER;

--2. Manual Failover from Secondary server

ALTER DATABASE <Database_Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

Failover from primary server does not cuase any data loss but if you do it from secondary server then you are forcing secondary server to loss data. Data loss will only happend if primary server is not accesible while failover.

How to remove database mirroring session?

Use below script to remove database mirroring session. This script can be used on either primary server or on secondary server.

--Remove mirroring for database

ALTER DATABASE <Database_Name> SET PARTNER OFF;

How to stop and start databaes mirroring session?

Use below script to pause and resume the database mirroring session. You can SUSPEND mirroring session from either primary server or from secodary server. But It can only be RESUMEd from primary server.

--Pause/Suspend database mirroring session

ALTER DATABASE <Database_Name> SET PARTNER SUSPEND;

--Resume database mirroring session

ALTER DATABASE <Database_Name> SET PARTNER RESUME;

How to add/remove witness server to/from already running database mirroring session?

Use below script to add or remove witness server to/from already running database mirroring session

--Add witness server to database mirroring session

ALTER DATABASE <Database_Name> SET WITNESS <witness_server>;

--Remove witness server from database mirroring session

ALTER DATABASE <Database_Name> SET WITNESS OFF;

SET WITNESS is only allowed on primary server.

Convert rows into columns using PIVOT and columns into rows using UNPIVOT

2013-03-13T15:16:00.001+00:00

You might have used pivot functionality in Microsoft Excel. Same you can do in MS SQL using PIVOT and UNPIVOT statement. PIVOT is used to convert values in rows to use as column header and UNPIVOT does the reverse of PIVOT. See below example for better understanding.

--PIVOT examle

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

DROP TABLE #sales

CREATE TABLE #sales (ProductName VARCHAR(200), SaleDate DATE, SalesQty INT)

INSERT INTO #sales(ProductName, SaleDate, SalesQty)
      VALUES('Bike', '2013-01-04', 56)
      ,('Bike', '2013-02-25', 45)
      ,('Bike', '2013-01-26', 87)
      ,('Bike', '2013-03-01', 12)
      ,('Bike', '2013-05-04', 62)
      ,('Bike', '2013-05-04', 73)
      ,('Bike', '2013-04-27', 34)
      ,('Scooter', '2013-03-12', 13)
      ,('Scooter', '2013-04-05', 75)
      ,('Scooter', '2013-01-09', 24)
      ,('Scooter', '2013-01-16', 76)
      ,('Scooter', '2013-01-16', 23)
      ,('Scooter', '2013-05-14', 36)
      ,('Scooter', '2013-01-16', 68)
      ,('Scooter', '2013-02-16', 88)

SELECT ProductName, Jan, Feb, Mar, Apr, May

FROM
      (SELECT ProductName, LEFT(DATENAME (month, SaleDate), 3) [monthName], ISNULL(SalesQty, 0) AS SalesQty FROM #sales) AS A
PIVOT
      (
      SUM(SalesQty) --Aggregate function
      FOR [monthName] IN (Jan, Feb, Mar, Apr, May) --Column list
      ) AS B

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

DROP TABLE #sales

PIVOT has two important section first is aggregate function (see comment in above query) and second is FOR section which contains list of column names and original column which contains the column names.

See below for UNPIVOT example. I have used ouput of PIVOT query as input of UNPIVOT query.

--UNPIVOT example

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

DROP TABLE #sales

IF OBJECT_ID('tempdb..#sales_PIVOT') IS NOT NULL

DROP TABLE #sales_PIVOT

CREATE TABLE #sales (ProductName VARCHAR(200), SaleDate DATE, SalesQty INT)

INSERT INTO #sales(ProductName, SaleDate, SalesQty)

VALUES('Bike', '2013-01-04', 56)

,('Bike', '2013-02-25', 45)

,('Bike', '2013-01-26', 87)

,('Bike', '2013-03-01', 12)

,('Bike', '2013-05-04', 62)

,('Bike', '2013-05-04', 73)

,('Bike', '2013-04-27', 34)

,('Scooter', '2013-03-12', 13)

,('Scooter', '2013-04-05', 75)

,('Scooter', '2013-01-09', 24)

,('Scooter', '2013-01-16', 76)

,('Scooter', '2013-01-16', 23)

,('Scooter', '2013-05-14', 36)

,('Scooter', '2013-01-16', 68)

,('Scooter', '2013-02-16', 88)

SELECT ProductName, Jan, Feb, Mar, Apr, May

INTO #sales_PIVOT

FROM

(SELECT ProductName, LEFT(DATENAME (month, SaleDate), 3) [monthName], ISNULL(SalesQty, 0) AS SalesQty FROM #sales) AS A

PIVOT

(

SUM(SalesQty)

FOR [monthName] IN (Jan, Feb, Mar, Apr, May)

) AS B

SELECT ProductName, [MonthName], SalesQty

FROM

(SELECT ProductName, Jan, Feb, Mar, Apr, May FROM #sales_PIVOT) AS A

UNPIVOT

(

SalesQty FOR [monthName] IN (Jan, Feb, Mar, Apr, May)

) AS B

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

DROP TABLE #sales

IF OBJECT_ID('tempdb..#sales_PIVOT') IS NOT NULL

DROP TABLE #sales_PIVOT

One important point to remember here is UNPIVOT is reverse process of PIVOT, but only limited for conversion of columns to rows. It does not regenerate the original result. This is due to aggregate function used by PIVOT. In our example we have used SUM function and there is no reverse function available for SUM to generate original value. There are some senarios where we can generate the original output using UNPIVOT. Please post if you have such example.

Script to get the dababase backup history.

2013-03-08T16:35:00.004+00:00

Often I have to get the backup history for reporting purpose. So I have writtent small script which give all details of database back history which is required for my report. I have used backupset and backupmediafamily tables to get the database backup history. Both of these tables resides in msdb database. So for some reason if you have restored msdb database. Then it will show history till the restored database time. Please see below script to get the database backup history.

SELECT

A.database_name

,CASE A.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS backup_type

,B.physical_device_name

,CAST(CAST(A.backup_size / (1024*1024) AS INT) AS VARCHAR) + ' ' + 'MB' AS size

,CAST(DATEDIFF(S, A.backup_start_date, A.backup_finish_date) AS VARCHAR) + ' ' + 'Seconds' time_taken

,A.backup_start_date

FROM msdb.dbo.backupset A

INNER JOIN msdb.dbo.backupmediafamily B ON A.media_set_id = B.media_set_id

ORDER BY A.database_name

, backup_start_date DESC

This script gives history of all database, if you need details for only one database then restict result by using WHERE caluse on database_name field.

For more information on backupset and backupmediafamily tables, see below links.

http://msdn.microsoft.com/en-us/library/ms186299.aspx

http://msdn.microsoft.com/en-us/library/ms190284.aspx

How to load data from PDF file in SSIS package using script component

2013-03-08T16:08:00.001+00:00

I have come across the case where I have to read PDF document, parse it and write content to database using SSIS. PDF document is not usual source for any data so there is no component available to read PDF file from SSIS. I have used iTextSharp library in Script Component to read the PDF file from SSIS package. I also found very good link where I found good example on how to use iTextsharp library.

Using iTextsharp library it is very easy to process PDF document. In my case it was simple case of reading PDF document page by page and parses the content using custom logic. You can do much more than just reading PDF document using this library.

To read data from PDF file and write to database, I have used Script Component as data source, which reads the PDF document and return result and output record of Script Component. The simply used OLE DB destination to write it to database. You might be aware that any third party dlls can be used in SSIS Script component by referencing it. But to add it as reference and use it in script component you have to register dll in GAC. It can be referenced without adding it but dll might not be available on the production environment at the same location. So better add them to GAC. In my case I was not allowed to add dll to production server directly, so I used .Net reflection to load the dll dynamically and added dll location as configuration in SSIS package.

Below is my code

//Below two variable are used to pass ITextSharp dll location and PDF file path.

//Which ultimately supplied from dts config file.
String configITextPath = Variables.itextdllfilepath;
String configSourceDataFile = Variables.filepath;

//Load ITextSharp library

Assembly assembly = Assembly.LoadFrom(configITextPath);

//Get PdfReader object and create its instance

Type readerType = assembly.GetType("iTextSharp.text.pdf.PdfReader");
object reader = Activator.CreateInstance(readerType, new object[] { configSourceDataFile });

Type extractorType = assembly.GetType("iTextSharp.text.pdf.parser.PdfTextExtractor");

Type extractionStrategyType = assembly.GetType("iTextSharp.text.pdf.parser.LocationTextExtractionStrategy");

object extractionStrategy = Activator.CreateInstance(extractionStrategyType);

//Get number of pages in PDF document

Int32 pages = (Int32)reader.GetType().GetProperty("NumberOfPages").GetValue(reader, null);

//Read PDF document page by page and process it and generate Output rows for script component.

String pageText;
List<PepRecord> pepRecords = new List<PepRecord>();
for (int page = configStartPageNumber; page <= pages; page++)
{
    reader = Activator.CreateInstance(readerType, new object[] { configSourceDataFile });
    extractionStrategy = Activator.CreateInstance(extractionStrategyType);
    pageText = (String)extractorType.GetMethods()[0].Invoke(reader, new Object[] { reader, page, extractionStrategy });

   //code to process PDF file content and create Output rows for script component
}

I know better way to do this is to add dll in GAC and refer from there. But in my case I have to quickly found the solution to process few PDF document suing SSIS package, so I have used reflection to load it.

How to use a Script Component as a Data Source in SSIS

2013-03-07T10:12:00.004+00:00

Script component in SSIS can be used as Data Source, Data Transformation and Data Destination. Here is small tutorial on how to use Script Component as data source in SSIS package. See below step by step guide to add script component as data source in SSIS data flow task.

1. Add Data flow task to Control Flow of your package.

2. Right Click on Data Flow Task and click on Edit.

3. Add Script Component to your Data Flow. It will popup window to ask to set the role of script task in data flow (see below image).

4. Select Source click Ok.

5. Now right click on Script Component and click on Edit.

6. Click on Inputs and Oputputs. Add few columns to default output (Output 0). You can change name of the output but I kept it as default for this example. If you want script task to return multiple data tables, then you can add more outputs and their columns.

7. Now Add few columns to your Output. Here I have added 3 columns to Output 0 named Column, Column 1 and Column 2. You can change name and data type of the column from right side property window. I have kept the default name and default type (Integer) for this example.

8. Click on Script tab and click on Edit Script… button to add the rows to your output.

9. Edit Script will open new visual studio project and opens main.cs file (for C#, it will be main.cv for VB). This file contails ScriptMain class, which has 3 predefined methods as shown below.

public override void PreExecute()

This method is used to perform any prexeuction task like opening connection, initialization valiables etc.

public override void PostExecute()

This method is used to perform any posexecution task like closing connection, dispising objects etc. This method also be used to set ReadWrite variable defined in your script.

public override void CreateNewOutputRows()

This method is used to actually returns the rows to your ourput. See below example where I have added 10 rows to our Output 0

public override void PreExecute()

{

base.PreExecute();

Add your code here for preprocessing or remove if not needed

}

public override void PostExecute()

{

base.PostExecute();

Add your code here for postprocessing or remove if not needed

You can set read/write variables here, for example:

Variables.MyIntVar = 100

}

public override void CreateNewOutputRows()

{

Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

for (int i = 0; i < 10; i++)

{

Output0Buffer.AddRow();

Output0Buffer.Column = (i + 1);

Output0Buffer.Column1 = (i + 1) * 10;

Output0Buffer.Column2 = (i + 1) * 100;

}

10.Add sample code as shown above and close visual studio project for script component. Click on Script Component Edit dialog window. Now your script task is ready to use as Data Flow Source. Start using it as normal source component.

How to do case sensitive comparision in SQL server

2013-03-07T09:03:00.003+00:00

Case sensitive string comparison can be done using collation in WHERE statement or by specifying collation for column.

See below example which uses COLLATE keyword while comparing two string values in WHERE clause. This example uses SQL_Latin1_General_CP1_CS_AS. If your data is not in English, then choose appropriate collation.

CREATE TABLE testUserTable (Username VARCHAR(30), pwd VARCHAR(30))

INSERT INTO testUserTable(Username, pwd)

VALUES('User 1', 'PassWord1'), ('User 2', 'PassWord2')

DECLARE @username VARCHAR(30)= 'User 1'

,@pwd VARCHAR(30)= 'password1'

--Query 1: This query will return result

SELECT *

FROM testUserTable

WHERE Username = @username

AND pwd = @pwd

-- Query 2: This query will not return result

SELECT *

FROM testUserTable

WHERE Username = @username

AND pwd = @pwd COLLATE SQL_Latin1_General_CP1_CS_AS

DROP TABLE testUserTable

First SELECT query will return result even if password is supplied in lower case. Second query will not return any record, which is actually what we wanted in this particular case.

Here in our example we have specified _CS_ (case sensitive) in our collation selection. This behaviour can be reverted to previous by setting it to _CI_ (case insensitive). This will give use the same result as we get in Query 1 because default collation setting in _CI_.

See below CREATE statement which uses collation while creating table.

CREATE TABLE testUserTable (Username VARCHAR(30), pwd VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS)

In this case we don’t have to specify collation in where clause. Both SELECT queries will give the same result.

Delete duplicate records from MS SQL table using CTE and window function

2013-02-25T15:41:00.002+00:00

I work for data company which receives data from many third party data providers. These data often comes with duplicate records. so while I import them to our database, I ofter has to run de-duplication on those data. I have tried many de-deuplicate methods. Here I would like to share those methods.

To see those method lets take some example data. It is City data. I have simplified this data to contain only few columns. Use below script for sample data.

CREATE TABLE City(CityName VARCHAR(100), CityCode VARCHAR(100), County VARCHAR(100), Country VARCHAR(100), CreateDate DATETIME)

INSERT INTO City(CityName, CityCode, County, Country)

VALUES ('London', 'LN', 'n/a', 'England', '2013-02-25 15:08:00')

,('London', 'LN', 'n/a', 'England', '2013-02-25 15:09:00')

,('Liverpool', 'LP', 'Mersyside', 'England', '2013-02-25 15:10:00')

,('Liverpool', 'LP', 'Mersyside', 'England', '2013-02-25 15:11:00')

,('Liverpool', 'LP', 'Mersyside', 'England', '2013-02-25 15:12:00')

,('Oxford', 'OX', 'Oxfordshire', 'England', '2013-02-25 15:13:00')

,('Oxford', 'OX', 'Oxfordshire', 'England', '2013-02-25 15:14:00')

,('Oxford', 'OX', 'Oxfordshire', 'England', '2013-02-25 15:15:00')

,('Oxford', 'OX', 'Oxfordshire', 'England', '2013-02-25 15:16:00')

,('Glasgow', 'GL', 'n/a', 'Scotland', '2013-02-25 15:17:00')

,('Glasgow', 'GL', 'n/a', 'Scotland', '2013-02-25 15:18:00')

Now lets see the methods to de-duplicate these data.

1) Most simple method is to load data in staging table and use DISTINCT to select the unique records.

CREATE TABLE City_temp(CityName VARCHAR(100), CityCode VARCHAR(100), County VARCHAR(100), Country VARCHAR(100), CreateDate DATETIME)

INSERT INTO City_temp(CityName, CityCode, County, Country)
SELECT DISTINCT CityName, CityCode, County, Country FROM City

TRUNCATE TABLE City

INSERT INTO City(CityName, CityCode, County, Country, CreateDate)
SELECT CityName, CityCode, County, Country, GETDATE() FROM City_temp

These will work without CreateDate field, but if we have to select the createDate also then you have to use aggregate function like MIN/MAX.

INSERT INTO City_temp(CityName, CityCode, County, Country, CreateDate)
SELECT CityName, CityCode, County, Country, MAX(CreateDate) AS CreateDate FROM City GROUP BY CityName, CityCode, County, Country

2) Use temp table with Id column with IDENTITY and select records with MIN/MAX value of Id

CREATE TABLE City_temp(Id INT IDENTITY(1,21), CityName VARCHAR(100), CityCode VARCHAR(100), County VARCHAR(100), Country VARCHAR(100), CreateDate DATETIME)

INSERT INTO City_temp(CityName, CityCode, County, Country, CreateDate)

SELECT CityName, CityCode, County, Country, CreateDate FROM City

TRUNCATE TABLE City

INSERT INTO City(CityName, CityCode, County, Country, CreateDate)

SELECT CityName, CityCode, County, Country, CreateDate

FROM City_temp AS A

INNER JOIN (SELECT MAX(Id) AS Id FROM City_temp GROUP BY CityName, CityCode, County, Country) AS B

ON A.Id = B.Id

3) This is the most effective method so far I found, using ETC and ROW_NUMBER() window function. Use below query to delete the duplicate records.

;WITH T
AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY CityName, CityCode, County, Country ORDER BY CityName, CityCode, County, Country) AS RowIndex
FROM City )

DELETE FROM T WHERE RowIndex > 1

This method is fast of above 2 methods becuase it does all processing in memory there is no need to wirte data to temp table. Please not here that processing in memory only happen if SQL server has enough memory to process these data, otherwise internally it wirtes data to tempdb.

I hope this will be useful. Please feel free to comment if you find any other more effective method to remove duplicate records in SQL server.

Free E-Books from Microsoft on SQL Server 2012

2013-02-25T11:30:00.001+00:00

Microsoft giving away some usefull E-books for free on various SQL Server topics. These books are available on Microsoft Technet website. Those are available in various formats (EPUB, MOBI and PDF). You can download using below links.

Here is the current available book list.

Books Online: Backup and Restore of SQL Server Databases
Books Online: Data Analysis Expressions (DAX) Reference
Books Online: Data Mining Extensions (DMX) Reference
Books Online: Data Quality Services
Books Online: High Availability Solutions
Books Online: Master Data Services
Books Online: Monitor and Tune for Performance
Books Online: Multidimensional Expressions (MDX) Reference
Books Online: SQL Server Distributed Replay
Books Online: Transact-SQL Data Definition Language (DDL) Reference
Books Online: Transact-SQL Data Manipulation Language (DML) Reference
Books Online: XQuery Language Reference
Introducing Microsoft SQL Server 2008 R2
Introducing Microsoft SQL Server 2012
Master Data Services Capacity Guidelines
Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster RecoveryMicrosoft SQL Server Analysis Services Multidimensional Performance and Operations Guide
QuickStart: Learn DAX Basics in 30 Minutes
SQL Server 2012 Tutorials: Analysis Services - Data Mining
SQL Server 2012 Tutorials: Analysis Services - Multidimensional Modeling
SQL Server 2012 Tutorials: Analysis Services - Tabular Modeling
SQL Server 2012 Tutorials: Reporting Services
SQL Server 2012 Tutorials: Writing Transact-SQL Statements
SQL Server 2012 Upgrade Technical Guide

E-Books are aslo available for other technology. Please see below link for other available books.

Microsoft E-books for Free

Script to get current running query from Stored Procedure / Trigger / Function

2013-02-21T12:25:00.000+00:00

I have written small script which gives you the current running query from stored procedure, trigger or function. Working on big database processing I ofter come across stored procedures (SPs) which takes hours to complete. These SPs has many small queries (INSERT/UPDATE/DELETE/MERGE) or call to other SP or call function. Mostly I use RAISEERROR WITH NOWAIT or PRINT to know the progress of SP but there are some instances where I cannot change SP to add progress reporting statement. So wrote below script to know the current running query. It is very useful to know current running query and helped me in many data processing problems.

SELECT

SUBSTRING(B.[text]

, (A.statement_start_offset / 2) + 1

, (((CASE WHEN A.statement_end_offset <= 0 THEN DATALENGTH(B.[text])

ELSE A.statement_end_offset END)

- A.statement_start_offset) / 2 ) + 1

) AS statement_text

FROM sys.dm_exec_requests A

CROSS APPLY sys.dm_exec_sql_text(sql_handle) B

WHERE session_id = 56 --Use you session id here.

Please note that above script will only tells you which query is running, not the actual progress of the query. Unfortunatly there is no way to know the actual progress of the query :(.

You can use Activity Monitor from management studio or use sp_who2 or below query to know the session id

SELECT * FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Plesae comment if you like this script or if have better script/way or if you find any error in this script or any aditional information about this.

Display data and index space usege for each table in MS SQL

2013-02-21T11:51:00.002+00:00

I have came across a requirement to generate regular report to for the data and index space usage for each table as well as for database. So I have wirttern a small script to do it using Sp_spaceused stored procedure. My script is posted below which might be useful to you. If you find useful please comment in comment section. And feel free to post about correction or if you find better script for this. Data and index size in result set is in MB, it can be changed to Bytes/KB/GB using changes in SELECT query at the end. Below script is self descriptive (at least I think so!!!) If you need any help please leave comment.

DECLARE @counter INT

DECLARE @max INT

DECLARE @name VARCHAR(100)

DECLARE @t TABLE (id INT IDENTITY, name VARCHAR(100))

DECLARE @t1 TABLE (name VARCHAR(100), [rows] INT, reserved VARCHAR(100), data VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100))

DECLARE @t2 TABLE (name VARCHAR(100), [rows] BIGINT, reserved BIGINT, data BIGINT, index_size BIGINT, unused BIGINT)

INSERT INTO @t (name)

SELECT s.name + + '.' + t.name FROM sys.tables AS T INNER JOIN sys.schemas AS S ON S.schema_id = t.schema_id WHERE [type] ='u'

SET @max = @@ROWCOUNT

SET @counter = 1

WHILE @counter <= @max

BEGIN

SELECT @name = name FROM @t WHERE id = @counter

INSERT INTO @t1(name, [rows], reserved, data, index_size, unused)

EXEC Sp_spaceused @name

      SET @counter = @counter + 1
  INSERT INTO @t2(name, [rows], reserved, data, index_size, unused)
  SELECT @name
        , CAST([rows] AS BIGINT)
        , CAST(REPLACE(reserved, ' KB', '') AS BIGINT)*1024
        , CAST(REPLACE(data, ' KB', '') AS BIGINT)*1024
        , CAST(REPLACE(index_size, ' KB', '') AS BIGINT)*1024
        , CAST(REPLACE(unused , ' KB', '') AS BIGINT)*1024
  FROM @t1

  DELETE FROM @t1

END

SELECT SUM(reserved)/1024/1024 reserved

, SUM(data)/1024/1024 data

, SUM(index_size)/1024/1024 index_size

, SUM(unused)/1024/1024 unused FROM @t2

SELECT name AS table_name, data_size, index_size, (data_size + index_size) AS total_size

FROM (

SELECT name, data/1024/1024 AS data_size, index_size/1024/1024 AS index_size FROM @t2

SSIS Tutorials : Debugging Data Flow

2011-08-03T15:25:00.000+01:00

BIDS tool has option to debug the Data Flow control. In normal cases you can see row count while you execute the package in debug mode. And you can see the execution details in progress (Execution Result) tab of the package designer. Apart from this you can see data which is being manipulated in you data task using data viewer. Let's see how to use data viewer in Data Flow task.

Step 1: Define your Data Flow Task. See below image for sample data fow task.

Step 2: Right Click on Data Flow Path editor as show in below image. And the click on Edit or Data Viewer.

Step 3: Click in on Add as show in above image. to add data viewer. It will open sceen as shown below to select the type of data viewer you want. For Grid view just click on ok and Ok. It will add one data viewer to your data flow.

Step 4: After adding data viewer you will see small viewer icon along with data flow path.

Step 5: Now run your package to see the data viewer in action. Once you run the package, it will stop at data viewer and will show the records. You will see tree buttons on data viewer. Play, Detach, Copy Data. Click on play to continue exectution with data viewer attached to data flow path. Click on Detach to detach data viewer from execution flow. and Copy Data to copy data in clipboard.

This was short explanation to use data viewer to debug the Data Flow task.

Data viewer can display data in different format. It can show data in grid, histogram, scatter plot and column chart.

Data Grid : It allows column selection, which can later appear as column in tabular data display in debug mode.

Histogram : It only works for numeric column and shows data distribution as histogram.

Scatter plot : This one also only works with numeric data. You can select numberic data column for different axis for the graph.

Chart format: This chart show the occurrence count of discrete values in the selected column.

MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | How to debug package | Debugging Packages | Debugging Data Flow | How to Debug SSIS Package

SSIS Tutorials : How to Debug SSIS Package

2011-07-30T07:32:00.000+01:00

Debugging SSIS package is possible using Business Intelligence Development Studio. There are way of debugging the different component of package. Data Flow, Control Flow and Script Task can be debugged using different technique.

Debugging Control Flow: Control flow can be debugged using breakpoints, debugging windows and progress reporting.

Debugging Data Flow: Data Flow can be debugged using data viewer and progress reporting.

Debugging Script Task: Script Task can be debugged using break point in script code. It is same as debugging any .Net code debug.

Apart from above debugging methods you can use different event to write log or send message to know the status of the package execution. Above techniques can only be used while you are executing package using Business Intelligence Development Studio. But if you want to do it on production server (means package which are running out side of development studio), then only way is to generate proper log of you package using package event or adding extra task item in your control flow or data flow.

Next we will see how to use above debugging technique in details in coming blogs.

MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | How to debug package | Debugging Packages

SSIS Tutorials : First Package

2011-07-29T11:50:00.000+01:00

Start SQL Server Business Intelligence Development Studio

Create New Project of type Integration Services Project

You will see default package designer opened as shown below.

BNow your package is ready to run. You run your package using F5 button or using Start Debugging menu item. As this package does not contain anything but still you can run it. Now let see the main components of Package designer.

Any package designer has below main section.

Control Flow
Data Flow
Event Handlers
Package Explorer
Execution Result or Progress : (This will only apper if you run the package at least once.

Now lets see the purpose of each sections.

Control Flow: This section contains the main exection work flow of the package. You can define you package execution steps and sequences here. It contains Tasks and Constraints.

Data Flow: This section contains details of Data Flow task like Data Flow Source, Data Flow Transformationa and Data Flow Destination. In short you can read data from source system, apply some trasformation and write back to destination system.

Event Handlers: This section is used to defined the task to be perrformed in case of error or some event like Task Start, Task End, Variable Chaned etc.

Package Explorer: Package explorer gives you the opportunity to see pakage as tree view. It shows all details about package which you see in all other sections.

Execution Result or Progress : This section only appers if you run the packages at least once. Progress section appears while package is running and it changed to Execution Rerult when you stop exection of the package. This section show the details and status of each Task execution.

Apart from above section Package has some more section which are important.

Connection Managers: This section appears only while you are in Control Flow or Event Handler Section. This section shows all connection available to package.

Vaiables: This section shows variable defined at package level or Task level. You can see veriable by Clicking on Variables menu on context menu of Control Flow designer or any Control Flow Item. You can also see variable in Package Explorer section under Variables node.

Aprat from above you can see Property window and Toolbox as you see with any Visual Studio application.

Next we will see how to use different section and details about different Control Flow Items and Data Flow item.

MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | SQL Server Integration Service (SSIS) Package design

Journey of SQL Server

2011-07-28T20:58:00.000+01:00

Today we all know and use Microsoft SQL Server 2008R2, latest release from Microsoft. But some of us don't know about the history of SQL server. So I thought let start from it.

Here is the major landmark in the life of SQL Server

1987: Sybase Corporation released its first relational database system under the name of Sybase SQL Server for UNIX.
1988: Sybase, Microsoft and Ashton-Tate co-developed SQL sever for OS/2 for PC.
1993: Till this year Microsoft and Sybase has co-developed and release SQL Server 4.21, then co-development ended. Microsoft released SQL server 4.2.1 under the code name SQLNT for Windows NT.
1995: SQL Server 6.0, first Microsoft only version released under the code name SQL95.
1996: SQL server 6.5 released under the code name Hydra. Enterprise Manager (StarFighter I) and SQL Server Agent (Star Fighter II) born with this version release.
1997: SQL Server 7.0 released under the code name Sphinx. This version has included Replication, OLAP services, Full Text Search.
2000: SQL Server 2000 released with code of Shiloh. It came with features like Indexed Views, Distributed Partitioned Views, Clustering, XML Support, ser Defined function, Reporting Services, Data Mining Tool etc.
2003: SQL Server 2000 64 bit version released. Code name Liberty.
2005: SQL Server 2005 released under the code name of Yukon. Which came with lots of new feature
2008: SQL Server 2008 released under the code name of Katmai. See this for details.
2010: SQL Server 2008 R2 released under the code name of Kilimanjaro. SQL Server Azure released under the code name of Matrix.
Next coming version is under the code name of denali.

Hello World

2011-07-28T19:25:00.000+01:00

Since very long I was thinking to write my blog but I could not start writing probably because of my laziness. Finally today I took a wise decision to write my own blog. I am not sure how long I will continue writing but now I will try to write something on regular basis. If you have come this blog then please do write comment, so that I will motivation to continue writing. You might be thinking that why only today I took this decision. Even I don't know why. So like me don't think and enjoy(!!!) reading my blog.

I think I said enough about reason for starting blog. Now let me tell you what I am gonna write in my blog. From the name (SQL31) you might have guessed that it is something about SQL. You are right, I have some knowledge about SQL which I want to share here. If it is about SQL then what is 31? There is no magic about it. It is my birth day. I was typing some name for my blog and could not find good word so I said let me try this and it was available. That's it. It is SQL31. Good name! Isn't it?

No let me tell you about my self. I am a SQL Server DBA/Developer since 2003. I have worked on SQL server 2000, 2005, 2008, 2008R2, DTS, SSIS, SSAS, SSRS. Apart from SQL server I also have experience of .Net, C#, VB.NET, ASP.Net, ASP MVC, Silverlight, WPF, WCF, XML, HTML, DHTML, Javascript.

That's all for this post. Enjoy the following posts.