SSIS - Create a Simple ETL Package

Step 1: Open visual studio and create new SSIS Project.

Step 2: Add data Source.

Step 3:Here you can create connections. 

Step 4:Add Data Flow Task.

Step 5:Add other tools as shown in image.

Step 6:Create flow of data.

Step 7:Double click on "OLE DB Source" table and add Source table.

Step 8:Double click on "OLE DB Destination" and add Destination table.

Step 9: Check the mapping of both table and uncheck the check boxes.Add Row Per batch and click on ok.

Step 10: Right Click on the background and execute the task.

Output :

SQL Server - Remove Mail Log and Mail Attachment from MSDB

use msdb;

GO

DECLARE @DeleteOlder DATETIME

SET @DeleteOlder = DATEADD(week, -1, CURRENT_TIMESTAMP)--Till Previuos Week

EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @DeleteOlder 

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteOlder 

SSRS - Generating QR Code in SSRS Reports

Step 1:Create a data set using following Query.

Step 2: Use matrix and bind the dataset to it, add new rows and in that row add one image box.

Step 3: Right click on image and click on Image Property.

 Step 4: Click on expression button of "Use this image".

Step 5: Write the following Statement.<DataFieldName> is the field that you want encrypt it into QR Code.

Add this Statement:

 ="http://qrfree.kaywa.com/?l=1&s=8&d="+<DataFieldName>

Step 6:Here is the output, "Hello" and "Friend !" is in QR Code.

to check the result use QR Code Scanner.

SSRS - Change Report Manager Logo using CSS

Default Logo in SSRS Manager:

Step 1:

go to directory
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\images

and add your logo in image folder

Step 2:

go to directory
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\Styles

Open the ReportingServices.css file in any compatible Text Editor

Step 3:

Add the following CSS code at the end of the line to replace default LOGO with your Logo

TABLE.msrs-header TD.msrs-logo img{display:None}

TABLE.msrs-header TD.msrs-logo{BACKGROUND: url('../images/mylogo.jpg') no-repeat;background-size:auto;width:145px;}

/* width is additional, it depends on new image width */

SQL Server Database Space Utilization | Check Database Size in SQL Server

Step 1:

create table #tempA
(fileid int,
file_Size numeric(18,2),
space_used numeric(18,2),
free_space numeric(18,2),
name varchar(100),
filename_ varchar(1000))

-----------------------------------------------------------
Step 2:

insert into #tempA
exec sp_MSforeachdb 'use [?]
select
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a'

-----------------------------------------------------------
Step 3:

select A.database_name,
SUM(Log_size_mb)Log_size_mb,
SUM(Row_size_mb)Row_size_mb,
Sum(Total_size_mb)Total_size_mb,
SUM(space_used)space_used_MB,SUM(B.free_space)free_space_mb 
from (SELECT 
database_name = DB_NAME(database_id),name
, Log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, Row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, Total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
--WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id,name)A
left join #tempA B
on A.name=B.name
group by A.database_name

drop table #tempA

-----------------------------------------------------------
OutPut:

SSRS - Conditionally Hide Tablix / Matrix Row Total

1.    Select Entire Tablix Row to hide that contains Total.

2.    Right Click > Row Visiblity.

3.    Select Show and Hide based on condition.

4.    Write Expression.

5.    =IIF(CountDistinct(<Field_Name>)>1,False,True)

6.    Add Field_Name from table.

Note: On the <Field_Name> if the Count is greater then 1 then it will show total else it will hide the row.

SSRS - Alternate Change Background Color of Matrix Row

Step 1: Select entire Row to apply background color

Step 2: go to Background Color from Property Explorer.

Step 3: Click on Drop Down Box and go to Expression.

Step 4: Add the following Code. 

=IIF(RunningValue(<Field_Value>,COUNTDISTINCT,NOTHING) MOD 2 = 0
,"Red",
"White")

Here <Field_Value> is that field which have alternate changed value.

Result:

SSRS - MSDB Database Status | Check Space Utilization for MSDB

Check MSDB Database Status:

use msdb;

SELECT object_name(i.object_id) as objectName,

i.[name] as indexName,

Sum(a.total_pages) as totalPages,

sum(a.used_pages) as usedPages,

sum(a.data_pages) as dataPages,

(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,

(sum ( a.used_pages) * 8 ) / 1024 as usedSpaceMB,

(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB

FROM sys.indexes i

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

GROUP BY i.object_id, i.index_id, i.[name]

ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)

SSRS - Conditional Formatting of Text or Matrix / Table Cell Background | Rotate Text in SSRS

First Step:

Second Step:

Here in background color we write an expression:

   =IIF(SUM(Fields!DIFF.Value)<SUM(Fields!Target.Value),"Red",IIF(SUM(Fields!DIFF.Value)>SUM(Fields!Target.Value),"Green","Khaki"))

Result:

SSRS Drill Through Example

Step 1:

Step 2:

Result:

For navigation bar i have used table and set it background images(RED,BLACK,LIGHT BLUE Navbar Images).

and in every navigation tab i had pass the label name(cell value of the table) to the parameter.