Latest Event Updates

How to get the Previous and Next Row Data

Posted on Updated on

If you’ve ever wrote reports that requires comparing numbers from previous weeks, months, quarter, and year SQL 2012 and later versions will solve your issue.

In this example, I have a table call HBClaimsRemits_DL_NW. Everyweek I bring in new data into this table. However, I would like to see if the data grew or shrank.

To do this, we use LAG and LEAD. Both of these functions requires that we use PARTITION so that we’re getting the LAG and LEAD data for the specific group of data.

LAG gets the data from the previous record. If no previous record exists, a NULL value will return.

LEAD gets the next row data. If no future record exits, a NULL value will return

Here is the script to show Previous (LAG) and Future (LEAD) records.

SELECT
Name,
RecordCount AS Current_Record_Count,
ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Previous_Record_Count,
ISNULL(LEAD(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Next_Record_Count,
CreateDate
FROM SSIS_Log.dl.event

I like to simplify the results to something more of an Indicator (KPI).

Here is the script with an Indicator field. There’s more scripting involved but the end results is easier to read.

SELECT
Name,
RecordCount,
CASE
WHEN ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) = 0 THEN ‘No Changes’
WHEN RecordCount ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0)
THEN ‘Up by ‘ + CAST(FORMAT(RecordCount – ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0), ‘##,###’) AS VARCHAR(20))
END AS Indicator,
CreateDate
FROM SSIS_Log.dl.event

Truncate Transaction Log

Image Posted on Updated on

Hopefully, you’re not visiting this page in a panic because you or your users are not able to use the Database because it’s out of space. Keep your sanity and strategically plan out the database maintenance plan to prevent issues like this from surfacing.

Before you truncate the transaction log, please remember that if you want to recovery any data before the truncation, that data will no longer exists. Before you truncate the log files, you’ll need to know the exact file name. Here’s how you do that.

Go to the database properties

Database Properties
Database Properties

Under Files, you’ll see the file log name.

File Log Name
File Log Name

SQL 2005

USE MyDatabaseName
GO
BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (MyDatabaseName_Log, 1);
GO

SQL 2008, 2012 R2, 2014

Steer away from WITH TRUNCATE_ONLY in these versions because it no longer exists.

If your database is already set in SIMPLE recovery mode, then every time you do a backup, the transaction log will be automatically truncated for you.

If your database is in FULL recovery mode, then here are a couple of steps

USE MyDatabaseName
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (MyDatabaseName_Log, 1);
GO

If your database is already part of a maintenance place that backups the data then use this.

BACKUP LOG MyDatabaseName_Log TO DISK = N’C:\Backup\MyDatabaseNameDB.bak’
GO

Have a cup of BEETS juice and good luck!

Table Size

Posted on Updated on

There are times when you need to migrate data or scale out your database. Here is a script to get you how much space you’re actually using per Table.

Here are 3 methods but not limited.

1. Get a specific table size

EXEC sp_spaceused N’dbo.MyTable’

2. Get all table size

CREATE TABLE #TableSize(Name VARCHAR(100), Rows VARCHAR(50), Reserved VARCHAR(50), Data VARCHAR(50), IndexSize VARCHAR(50), Unused VARCHAR(50))
INSERT INTO #TableSize
EXEC sp_msforeachtable ‘EXEC sp_spaceused [?]’

SELECT * FROM #TableSize ORDER BY Name ASC

3. Custom Code

SELECT
TableName = t.Name
,SchemaName = s.Name
,[RowCount] = p.Rows
,TotalSpaceKB = SUM(a.Total_Pages) * 8
,UsedSpaceKB = SUM(a.Used_Pages) * 8
,UnusedSpaceKB = (SUM(a.Total_Pages) – SUM(a.Used_Pages)) * 8
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.OBJECT_ID > 255
GROUP BY t.Name
,s.Name
,p.[Rows]
ORDER BY t.Name

Alternate Row Color

Image Posted on Updated on

When you’re displaying data in a Table or Tabular, sometimes the report may look very busy to the eyes. By alternating the row colors, the results will look more easy on the eyes. Here’s how you do it.

Here’s our “Before” results
0

Step 1: Click on the Design tab

1

Step 2: Click on the detail row. In the BackgroundColor drop-down, select “Expression”

2

Step 3: Type this  =IIF(RowNumber(Nothing) MOD 2,”White“, “Silver“)

Step 4: Preview the report

3

Conditional Split

Image Posted on Updated on

Use Conditional Split if you need to separate the data from a source (Flat File, Excel, Table, etc.) into multiple destinations (Flat File, Excel, Table, etc.)

Step 1: Drop a Data Flow Task to the Control Flow

1
Data Flow Task

Step 2: Create 4 Flat File Connections. The first flat file is your source. You can use OLE, ODBC, etc.

This second flat file is for my all .txt data

This third flat file is for my all .cs data

This fourth flat file is for my all .aspx data

2
File File Connection Manager Editor Input File
3
Flat File Connection Manager Editor Output Text File
4
Flat File Connection Manager Editor Output CS File
5
Flat File Connection Manger Editor Output ASPX File

Step 3: Drop a Flat File Source into the Data Flow

6
Flat File Source

Step 4: Double click on the Flat File Source and establish the connection by selecting your source file.

7
Flat File Source Editor Connection Manager

Step 5: Drop a Conditional Split into the Data Flow. Drag the arrow from the Flat File Source to the Conditional Split.

8
Conditional Split

Step 6: Double click on the Conditional Split and create three conditions.

9
Conditional Split Transformation Editor

Step 7: Drop three Flat File Destination onto the Data Flow

10
Three Flat File Connections

Step 8: Drag the arrow from the Conditional Split onto one of the Flat File Destination. An Input Output Selection dialog will popup. Select one of the output.

11
Input Output Selection

Repeat this step for all Flat File Destination.

12
Repeat the connection to all three Flat File Connections

Step 9: Double click on the Flat File Destination and select the connection. Repeat this process to the other two Flat File Destination

13
Flat File Destination Editor

Step 10: Run the SSIS package and now you’ll have all three files with specific file types in them.

In this example, I’m using Flat Files but you can use Excel, ODBC, CSV, etc. The process is the same– just the connection setup is different.

ForEach Loop Container

Image Posted on Updated on

When you need to read through a file directory and save all of the file names to a text file, follow these steps.

Step 1: Create a variable to hold the file name that the ForEach Loop reads

Step 2: Create a Flat File Connection

2
File Connection Manger

Step 3: Drop a “Foreach Loop Container” to the Control Flow

3
Foreach Loop Container

 Step 4: Rename the “Name” text to something more meaningful

4
Foreach Loop Editor General

Step 5: In the Enumerator drop-down, select “Foreach File Enumerator” and pick the folder path. Note: If you check “Traverse subfolders”, the Foreach loop will go through all subfolders.

5
Foreach Loop Editor Collection

Step 6: We need to save our results to a variable if we want to know what files were scanned. Under the “Variable” drop-down, select “User::MyFileName”

6
Foreach Loop Editor Variable Mappings

 Step 7: Drop a “Script Task” into the Foreach Loop Container.

7
Script Task

Step 8: Double click on the “Script Task”. In the “ReadOnlyVariables”, select “User::MyFileName”. Click on “Edit Script”

8
Script Task Editor Script

Step 9: Add the following C# code

public void Main()
{
ConnectionManager cm = Dts.Connections[“Output”];

string content = Dts.Variables[“User::MyFileName”].Value.ToString();

string fileDirectory = cm.ConnectionString;

System.IO.File.AppendAllText(fileDirectory, content + “\r\n”);

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

 Now run your SSIS and once completed, open up the Output.txt file to see the results.