Sql Server Data Tools – Project Deployment – Error – “Project consistency check failed”

Background

Using Microsoft’s SQL Server Data Tools ( SSDT).

Scenario

Trying to deploy a project …

image

but, running into the errors pasted below

Message Box

Image

failedToDeployTheProject_20180207_0826PM

 

Text

Failed to deploy the project.  Build errors were encountered.  For more information, see the Output Window.

Output Window

Image

deployProject_output_20180208_0741AM

 

Text

Error : Project consistency check failed.  The following inconsistencies were detected:

DataRefresh_package1 has a different ProtectionLevel than the project
DataRefresh_package2 has a different ProtectionLevel than the project

 

Remediation

Outline

  1. At the Project Level
    • Note and Adjust the Project’s ProtectionLevel to suit your needs
    • Ours is “DontSaveSensitive
  2. For each Package
    • Access the Package’s Property Window
    • Align the package’s ProtectionLevel to match the Project’s Protection Level

 

ScreenShot

Project

Project Protection Level

Screen – Original

Original Project Protection level is “Encrypt sensitive data with user key“.

Screen – Revised

Revised Project ProtectionLevel is “DontSaveSensitive“.

 

Additional Guidance

Project ProtectionLevel’s Changed, but Package ProtectionLevel left as is

Image

modifyProtectionLevelForEachPackage_20180207_0831PM

 

Text

The ProtectionLevel property of the project has changed.

To build the project, the packages need to have the same protection level as the project.

Open each package and modify the ProtectionLevel property.

Sql Server Data Tools – Integration Services Deployment Wizard–Error Message–“The variables were not found in the variable collection”

 

Background

Ran into problems deploying and running SSIS Packages.

Here is a brief write-up.

 

Scenario

Importing the package into SQL Server Data Tools.

And, also preparing to deploy it into .ispac file.

 

Error

Import Package into SSDT

Error :- Warning – The variables “$Project – Source [database collection]” were not found

Image

integrationServicesDeploymentWizard_Tab_SelectSource_FailedToDecrypt_20180206_0433PM

 

Textual

Warning: Failed to decrypt an encrypted XML node.
Verify that the Project was created by the same user.
Project load will attempt to continue without the encrypted information.

Package Deployment

Error :- Warning – The variables “$Project – Source [database collection]” were not found

 Image

deployPackage_subjectFailed_20180207_0226PM

 

Textual

 

Warning: The variables “Project-Source_Database_Connection” were not found in the variable collection.

The variables might not exist in the correct scope.

Error :

The expression for variable “Subject” failed evaluation.  There was an error in the expression.

 

TroubleShooting

Expression Builder

Accessed Expression builder for the variable, Subject, noted:

Image

expressionBuilder_20180207_0259PM

 

Text


"DataRefresh From "  +  SUBSTRING( @[$Project::Source_Database_Connection] , 13, 15) +" to " + SUBSTRING( @[$Project::Target_Database_Connection] , 13,15 )

 

Explanation

Noted that the expression makes reference to the Database Connection which can potentially contain sensitive data; such as server name and password.

 

Remediation

Outline

Correct package by:

  1. Removing potential sensitive data
  2. Change package properties
    • Security – “Protection Level”
      • Change from “EncryptSensitiveWithUserKey to “DontSaveSensitive”
  3. Place sensitive data in environment specific configuration files

 

Images

Package

Package – Property

Current
Image

package_properties_20180207_0554PM

 

Revised

Image

package_properties_ProtectionLevel_20180207_0601PM

Git – List Local Repositories

Background

It is so very easy to share code snippets via git.

And, so I find myself creating little tiny, targeted repositories.

Unfortunately, I have too many of them now.

 

Git Repositories

Local ones

List git repos from targeted folder

listLocalRepository.ps1

Code

param (
		[string] $folder)

		Set-StrictMode -Version 2

# Declare variables
$objListofFiles = $null;
$objListFileContentConfig = $null;

[string] $fileNameSought = $null;
[int]	 $iFileID = 0;
[string] $folderCurrent = $null;

[string] $urlLine = $null;
[string] $stringEmpty = [string]::Empty;

[string]$FORMAT_HEADER_FOLDER_TARGET = "Base Folder :-{0})"
[string]$FORMAT_PROCESSING = "{0}) {1}"
[string]$FORMAT_URL = "`t URL :- {0}"

[string]$FILE_CONFIG = "config";

#Looking for folders named .git
$fileNameSought = ".git"

# In config file look for url tag
$URL_TAG = "url";
$URL_PATTERN = "*" + $URL_TAG + "*";


# If folder not passed in, please use current folder
if ( `
		    ($folder -eq $null) `
		-or ($folder -eq $stringEmpty )`
	)
{

	$folder = (Get-Item -Path ".\" -Verbose ).FullName;

}

$strLog = $FORMAT_HEADER_FOLDER_TARGET -f $folder
Write-Host $strLog

Write-Host $stringEmpty;
Write-Host $stringEmpty;
		
# Where-Object {$_.GetType().Name -eq "FileInfo"} 
# Look for files and folders in path $folder
# Recurse
# Force	
# Show hidden files and folders, as well
# Attributes
#		:- Hidden
$objListofFiles = Get-ChildItem -Path $folder -Recurse -Force  -Attributes Hidden -Include $fileNameSought 

$iFileID = 0;

# enumerate the items array
foreach ($objFile in $objListofFiles) 
{

	#$element
	$iFileID = $iFileID + 1;


	# if the item is a container/directory, then process it.
	if ( $objFile.PSIsContainer)
	{

		# Get Current Folder
		$folderCurrent = $objFile.FullName;

		# display folder name
		$strLog = $FORMAT_PROCESSING -f $iFileID, $folderCurrent;

		Write-Host $strLog
		
		# Get config file in current folder
		$fileConfig = $folderCurrent + "\" + $FILE_CONFIG;
		
		# Read config file
		$objListFileContentConfig = Get-Content -Path $fileConfig
	
		# Look for URL tag	
		$urlLineRaw = $objListFileContentConfig | Where-Object {$_ -like $URL_PATTERN}

		# Strip out undesirables		
		$urlLine = $urlLineRaw;
		
		$urlLine = $urlLine -replace $URL_TAG, $stringEmpty
		$urlLine = $urlLine -replace "=", $stringEmpty
		$urlLine = $urlLine.trim()
		
		# Display URL Tag		
		$strLog = $FORMAT_URL -f $urlLine;

		Write-Host $strLog
		
		Write-Host $stringEmpty;
		Write-Host $stringEmpty;
	
		
	}
	  
}


 

Output

Source Control

Oops, just like Britney, did it again…create a new git repository

Git

GitRepositoryLocalList
Link

 

References

  1. Powershell
    • String
      • Empty String
        • Powershell – Create Empty String with powershell
          Link
      • Replace
        • Use PowerShell to Replace Text in Strings
          Link
    • Get-Content
      • If File or Folder
        • Powershell check if path is folder or file
          Link
        • How do I get only directories using Get-ChildItem?
          Link
    • Character
      • TAB Character
        • How do I type a tab character in Powershell
          Link
    • Array

 

Linux – Disk Utilization using du

Background

End of Year, Beginning of a new year, and end of Month are often good times to stress a computer system.

We have had our systems stressed these last couple of months.

Often it is running low on disk space, a lot of higher than normal database backup and log backups.

 

MS Windows

Microsoft Tools

On Windows I will often use Microsoft’s own Computer Management to review disks logical size and utilization.

Or File Explorer and filter by size looking for huge ones.

Space Sniffer

Or better still SpaceSniffer.

I spoke about SpaceSniffer here.

Linux

I know I am avoiding talking about Linux.

And, so let us talk about Linux a bit.

du command

Introduction

I suppose du means disk utilization.

Help

First let us get a quick overview of what du does and the options it offers.

Syntax


du --help

Output

Explanation

What does it do?

Summarize disk usage of each FILE, recursively for directories.

Options Tabulated

Option Meaning Usage
-a List files as well, not just directories Usage is du -a
-B Block Size -b 1 is default
-b 1024 means to divide the actual size by 1 K
-h Human readable  usage is du -h
–exclude=PATTERN Exclude files based on certain filename or extension du -a -B 1 -h –exclude=*.gz /dbbackup
 –max-depth  Our many folders down to go  du –max-depth=4 /dbbackup

 

Sample

Block Size ( -B)
Sample ( Block Size at 1 )

du -a -B 1 /dbbackup | grep "sql"

Output

Explanation
  1. /dbbackup/gregory/export.sql is 4096

 

Sample ( Block Size at 1024 )

du -a -B 1024 /dbbackup | grep "sql"

Output

Explanation
  1. /dbbackup/gregory/export.sql
    • was at 4096
    • But, now is 4
      • 4096/1024 => 4

 

Human Readable ( -h)
Sample ( Block Size at 1024 )

du -a -h *.sql

Output

Explanation
  1. /dbbackup/gregory/export.sql
    • was at 4096
    • But, now a more readable 4.0 K

 

exclude ( -exclude=filter)
Sample ( Exclude files already compressed via gzip )

du -a --exclude=*.gz

max-depth=N
Depth to show = 1

du --max-depth=1 -h /dbbackup

Output
 

Depth to show = 4

du --max-depth=4 -h /dbbackup

Output

Explanation
  1. In our first sample:
    • We progressed down only one folder tree from our asking node ( /dbbackup )
  2. In our second example:
    • We went down 4 levels from our root from /dbbackup

 

Use case scenario

Use case #1

Get Folder Size for our backup folder ( /dbbackup)

Baseline

Sample

du --max-depth=4 /dbbackup

Output

 

Silence Errors

There is an error for “permission denied“.

Let us silence it for now.

Sample

du --max-depth=4 /dbbackup 2>1 | grep -v 'permission denied'

Output

Explanation
  1. Using 2>1
    • We redirect errors such as permission denied to standard output
  2. Using grep -v
    • We filter out ‘permission denied’

 

Ordered Results

Let us order by size.

Sample

du --max-depth=4 /dbbackup 2>1 | grep -v 'permission denied' | sort -nr

Output

 

References

  1. StackOverflow
    • Exclude all permission denied messages from “du”
      Link

Ravi Zacharias – Sermons & Discussions – 2018/Feb

 

Videos

  1. Ravi Zacharias on Roman Catholicism
    • Is Roman Catholicism a cult? – Ravi Zacharias at Texas A&M’s Veritas Forum
      Dr. Ravi Zacharias answers the question, “Is Roman Catholicism another example of how unity does not equal uniformity within the Christian community or is it at its core a derivative of true Christianity?” at Texas A&M’s Veritas Forum on March 19, 2014.
      Channel :- Fiat Lux
      Published on :- 2014-March-20th
      Link
  2. Ravi Zacharias talks about Joel Osteen
    • Profile :- Ravi Zacharias talks about Joel Osteen and it’s not good. God Bless You Ravi keep up the good work.
    • Videos
      • Ravi Zacharias talks about Joel Osteen
        Channel :- Johnny OnTheSpot
        Published On :- 2018-Jan-4th
        Link
    • More
      • The full video is From “The 2007 Ligonier National Conference with Ravi Zacharias, Albert Mohler, R.C. Sproul, and R.C. Sproul Jr.
        • Mohler, Sproul, Sproul Jr., and Zacharias: Questions and Answers #1
          Link
  3. Ravi Zacharias and Dennis Prager with Jeff Foxworthy The Death of Truth, The Decline of Culture Q&A
    • Videos
      • Ravi Zacharias and Dennis Prager with Jeff Foxworthy The Death of Truth, The Decline of Culture Q&A
        Channel :- Bryan Caron
        Date Published :- 2017-Oct-17th
        Link
  4. Ravi Zacharias 2017 – Living A Life Used By God – DECEMBER 2017
    • Videos
      • Living A Life Used By God
        Channel :- Christian Sermons
        Date Published :- 2017-Dec-18
        Link

 

Hospice Care within the Prison System

Videos

  1. Justin on Serving LIFE
    • Profile
      • Watch Justin Granier serve as a Hospice Volunteer while he serves his mandatory life without parole sentence.
    • Videos
      • Part 1
        Published On :- 2017-Jan-16th
        Channel :- Maria Dunnigan
        Link
      • Part 2
        Published On :- 2017-Jan-16th
        Channel :- Maria Dunnigan
        Link

        • Participants
          • Volunteers
            • Justin Granier
            • Shaheed
            • Albert Lavalais
          • Cared for
            • Walter Chance
  2. Angola Prison Hospice : Opening the Door
    Published On :- 2011-Sept-8th
    Channel :- Open Society Foundations
    Link
  3. Free at last
    Profile :- This story is about the pioneering hospice program at the Louisiana State Prison and its impact on the culture of what was once called America’s Bloodiest Prison. Videography by Hank Bargine.
    Channel :- Pat Woodard
    Date Published :- 2013-Feb-6th
    Link

 

 

Indepth

Angola Prison Hospice : Opening the Door

Link
Half of the 5,000 inmates at the maximum-security Louisiana State Penitentiary at Angola are serving life sentences, and it is estimated that 85 percent of them will grow old and die there. Edgar Barens’s documentary examines one of the nation’s first prison-based hospice programs, a program that notably incorporates inmate volunteers into the care of other dying inmates.

Angola Prison Hospice: Opening the Door was produced by two former OSF programs, the Center on Crime, Communities & Culture and the Project on Death in America.

Quotes

Justin on Serving LIFE

Part 1

Part 2

  1. Burl Cain, Warden
    • Deeds
      • He did a horrible thing, he put somebody in the grave
      • But, I could not undo that
      • Nobody can
    • Best
      • We just do the best we can while they are here with us
      • And, so we are going to do the right thing
    • Care
      • Don’t nobody wants to be cared for
      • Don’t nobody wants to be forgotten
    • One another
      • So we are gonna to take care of one another
      • I am going to dig your grave and somebody else is going to dig mine
  2. Trainer Conversation
    • You suppose to love that dud
    • I thought he was going to be around a lot longer
    • There is not too much you can get
    • Now is just in you, you have gotten everything you can get from it
  3. Shaheed
    • Hospice Volunteer
    • Relationships
      • Importance of relationships, family relationships
      • Family Bonds
    • Self
      • I might be headstrong
      • I might want to live this thing out by myself
        • Like Mr. Fred did
    • I have been an idiot
    • Helping one live life out with love
    • Bible College
      • One year in already in Bible College
    • Burden
      • I don’t want to be a burden to you
    • Lesson Learnt
      • It takes a certain to degree to burn metal, just like it takes to burn water
      • I was filthy
      • I was an impure person
      • Now I am able to help myself
      • That is what I needed
  4. Kevin’s Passing
    • Volunteer #1
      • Seeing so many come see Kevin made me want to think that just may be in my last days others will come see me, as well
  5. Shaheed
    • I have heard Hospice refer to helping someone die with dignity
    • But, more that that, it is helping one live out there lives in love
  6. Burl Cain
    • For Others
      • It is the opposite of caring about self
      • And, that is the redemption
      • And, that is the rehabilitation
    • I
      • I did something good
      • I did something for somebody else
  7. Albert Lavalais
    • Giving back is what really matters
    • What I need I threw that out a long time ago
  8. Hospice Care
    • Snapin #1
      • Know that each patient is going to transition
      • And, he is going to do soon
      • He has an incurable disease
    • Snapin #2
      • Forgive in other to be forgiven
    • Snapin #3
      • Care
    • Snapin #4
      • Justin
        • Infraction
      • Ms. Sandy
        • Ms. Sandy thinks highly of you
      • Quit?
        • I will be quitting on the men I gave my word to
        • On the Street
          • More on medicin
        • Here
          • Taking care of the men
    • Snapin #5
      • Family
        • Son came to visit
      • Hard
        • Hard to see somebody wither away, but you got to be there
    • Snapin #6

 

SQL Server – Alerts – “The SQL Server performance counter ‘Data File(s) Size (KB)’ (instance ‘tempdb’) of object ‘Databases’ is now above the threshold”

Background

Getting quite a bit of email alerts from one of our SQL Server Instance.

Alert Notification

Image

Textual


DATE/TIME:
1/29/2018 9:17:09 AM

DESCRIPTION:
The SQL Server performance counter 'Data File(s) Size (KB)' (instance 'tempdb') of object 'Databases' is now above the threshold of 20000000.00 (the current value is 24428544.00).

Explanation

The message reads that our threshold is 20000000.00 and our current value is 24428544.00.

 

Interpretation

We will see later that the threshold and current current value are represented in KB.

Using Google we can convert KB to MB or convert to GB

Here we go:

  1. Convert to MB
    • Google Link
    • Threshold
      • 20000000.00 ( KB )
      • 20000 MB
    • Current
      • 24428544.00 ( KB )
      • 24428.544 MB
  2. Convert to GB
    • Google Link
    • Threshold
      • 20000000.00 ( KB )
      • 20 GB
    • Current
      • 24428544.00 ( KB )
      • 24.428544 GB

 

 

Alert Definition

GUI

TempDB Alert Properties

TempDB Alert Properties – Tab – General

Image

Explanation
  1. The alert states that if tempdb grows over 20000000 ( KB) or 20 GB an alert should be generated

 

TempDB Alert Properties – Tab – History

 

Metadata

Let us quickly review our current tempdb utilizations.

sp_helpdb

Let us use the most accessible built-in function, sp_helpdb

Code


exec sp_helpdb [tempdb]

Output

 

File utilization – Detail

List tempdb file utilization

Code


select 
          [fileID] 
            = tblSMF.[file_id]

        , [fileType]
            = tblSMF.[type_desc]

        , [fileSymbolicName] 
            = tblSMF.[name]

        , [filePhysicalName] 
            = tblSMF.[physical_name]

        , [fileState] 
            = tblSMF.[state_desc]

        , [isReadOnly]
            = case
				when (tblSMF.[is_read_only] = 1) then 'Yes'
				when (tblSMF.[is_read_only] = 0) then 'No'
				else 'unknown'
			  end


        /*

            , [fileSize] 
                = tblSMF.[size]

        */

        , [fileSizeIn8KPages] 
            = tblSMF.[size]

        , [fileSizeInMB] 
            = ( tblSMF.[size] * 8) / 1000

        , [fileSizeInGB] 
            = ( tblSMF.[size] * 8) 
                    / ( 1000 * 1000) 

        , [growth]
            = 
            (
                CASE is_percent_growth
                    WHEN 1 
                        THEN CONVERT(NVARCHAR(15), growth) + N'%'
                    ELSE 
                        CONVERT
                        (
                              VARCHAR(15)
                            , CONVERT
                                (
                                    BIGINT
                                    , ( tblSMF.[growth] * 8 )
                                        / 1000
                                )
                        ) 
                        + ' MB'
                END
            )

        --, tblSMF.*

from   sys.master_files tblSMF

where  (

            ( tblSMF.database_id = db_id('tempdb') )

       )

order by
          tblSMF.[type] asc
        , tblSMF.[name] asc

Output

 

 

File Utilization – Summary

Summarize tempdb file utilization

Code



;with cteFile
(
	  [fileTypeID]
	, [fileType]
	, [fileSize] 
)
as
(
	select 

			  [fileTypeID]
				= tblSMF.[type]

			,  [fileType]
				= tblSMF.[type_desc]

			, [fileSize] 
				= sum(tblSMF.[size])


	from   sys.master_files tblSMF

	where  (

				( tblSMF.database_id = db_id('tempdb') )

		   )

	group by
		  tblSMF.[type]
		, tblSMF.[type_desc]

)
, cteFileSummary
(
	[fileSize] 
)
as
(
	select 
		 [fileSize] 
				= sum(tblSMF.[size])

	from   sys.master_files tblSMF

	where  (

				( tblSMF.database_id = db_id('tempdb') )

		   )

)

select 
		  cteF.fileType

		, [fileSizeIn8KPages] 
			= cteF.fileSize

		, [fileSizeInGB] 
			= cast
				(
					( cteF.fileSize * 8 * 1.000) 
						/ ( 1000 * 1000) 

					as decimal(20, 2)
				)


		, [fileSizeTotalIn8KPages]
			= cteFS.fileSize

		, [fileSizeTotalInGB] 
			= cast
				(
					( cteFS.fileSize * 8 * 1.000) 
						/ ( 1000 * 1000) 

					as decimal(20, 2)
				)

		, [%]
			= cast
				(
					(
						(cteF.fileSize * 100.000 )
						/  cteFS.fileSize
					)
					as decimal(20, 2)
				)
				

from   cteFile cteF

cross apply cteFileSummary cteFS

order by
		cteF.fileTypeID




Output

Explanation

  1. File Utilization in tempdb
    • Data :- 19.92 GB
    • Log :- 1.05 GB
    • Total :- 20.97 GB

 

Summary

The alerts are valid, tempdb has grown to a little under 21 GB.

Our threshold is at 20 Gb.

Next in line, determine what is driving tempdb usage.