How to Handle SSIS Database movement from one environment to another

Below are the steps to follow the movement of SSISDB from one environment to another: -- opening the existing Database master key in SSIDB using password(We got this password from the I drive in production)
OPEN master KEY decryption BY password = 'SSISCatalogDBPassword'

go

-- Removing the additional encryption by service master key of backup instance SSISDB
ALTER master KEY DROP encryption BY service master KEY

go

-- Regenerate the service master forcefully in the restored instance SSISDB. We need to force it to work.
ALTER service master KEY force regenerate;

go

-- Add encryption by the newly regenerated master key of previous step
ALTER master KEY ADD encryption BY service master KEY

go 

Huge Performance Improvement by going for right column order in Composite Index

We had a situation where we had composite clustering key. The table had ~30 million rows. Composite clustering key (column1_lessSelective, Column2_MostSelective,Column3_Bit). The query was performing poorly, as joining the other tables with lessSelective column was slowing down the query. Changing the order of columns made the query to run pretty fast. composite clustering key (Column2_MostSelective, column1_lessSelective ,Column3_Bit). Now, Column2_MostSelective was used in JOINS and result was very high performing query. The time reduced from 9 minutes to 13 seconds.

How to create SQL Server linked server against cloudera Impala

Below are the step by step approach for making SQL Server Linked Server to work with Cloudera Impala. As SQL Server is 64 bit product, we have to install Cloudera Impala 64 bit ODBC driver to make it working with SQL Server linked service. We need to follow the below steps: 1. Install Cloudera Impala 64 bit driver in the same SQL Server machine and set up the DSN with below settings.

2. Now, create linked server in SQL Server with below settings. Product name, Datasource should be the same as the name created in DSN.

Now, the impala queries will work fine without issues, using SQL Server linked server. We had unicode data pull issues and we solved it by going for above settings.

Check Mirroring Status in SQL Server 2012

We can check mirroring status of the SQL Server 2012 using below command.

 SELECT   
     @@SERVERNAME as Server_Name,  
     DB_NAME(database_id) as Database_Name,   
     mirroring_state_desc,  
     mirroring_role_desc,  
     mirroring_safety_level_desc  
 FROM   
     sys.database_mirroring  
 WHERE   
 mirroring_role IS NOT NULL  

Getting free space information in SQL Server VM

We can get freespace information in a SQL Server VM using below TSQL command.

 SELECT DISTINCT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
   volume_mount_point [Disk],   
   file_system_type [File System],   
   logical_volume_name as [Logical Drive Name],   
   CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes  
   CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],   
   CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]   
 FROM sys.master_files   
 CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)  

Alternate way for FULL OUTER JOIN

Below is an one more way of doing FULL OUTER JOINs involving multiple tables. This gives a simpler approach, instead of having multiple OR conditions in subsequent tables.

1:  DECLARE @a table(id int, data int)  
2:  DECLARE @b table(id int, data int)  
3:  DECLARE @c table(id int, data int)  
4:    
5:  insert into @a values(1, 9), (2, 8),(3,7)  
6:  insert into @b values(2, 6), (3, 5)  
7:  insert into @c values(1, 4), (2, 5)  
8:    
9:  ;WITH CTE_IDs as  
10:  (  
11:  SELECT ID from @a  
12:  UNION  
13:  SELECT ID FROM @b  
14:  UNION  
15:  SELECT ID FROM @c  
16:  )  
17:  SELECT c.ID,t.* FROM CTE_IDs as c  
18:  cross apply  
19:  (  
20:  VALUES((select data from @a where id = c.id),  
21:  (select data from @b where id = c.id),  
22:  (select data from @c where id = c.id))   
23:  ) as t(a_data,b_data,c_data)  

Saving CSV file in UTF8, Compress and Archive them

We had a requirement, where the CSV files generated by SSIS were not in UTF8. We had to resave in UTF8 format. We also had to compress the group of files and archive the original set of files. We achieved that using Powershell.

 $CSVfilesFolder = "C:\CSVFiles\"  
 $CompressFolder = "C:\CSVFiles\Compress"  
 $ArchiveFolder = "C:\CSVFiles\Archive"  
 $filesForZip = Get-ChildItem -Path $CSVfilesFolder -Filter *.csv | Sort-Object LastWriteTime | Select-Object -First $CountofFilesToZip | select fullname | ForEach-Object { $_.FullName }  
 $newFileName = "$($CompressFolder)\GeneratedZipFile.zip"  
 #Encoding to UTF8   
 foreach($file in $filesForZip)  
 {  
   $UTF8Only = New-Object System.Text.UTF8Encoding($false)  
   $FileContent = Get-Content -Path $file  
   [System.IO.File]::WriteAllLines($file, $FileContent, $UTF8Only)     
 }  
 #Generate Zip file  
 $compress = @{  
  Path = $filesForZip  
  CompressionLevel = "Fastest"  
  DestinationPath = $newFileName  
 }  
 Compress-Archive @compress -Force  
 #Archive the files  
 foreach($file in $filesForZip)  
 {  
   Move-Item $file -Destination $ArchiveFolder -Force  
 }  

Maintain timestamp of CSV File

We were having a scenario, where we had to fix few CSV field issues. But, we also wanted to retain timestamp. We used below script to fix the CSV and maintain the timestamp.

 $BulkLoadFolder = "C:\Projects\Sntrax\Bulkload\FilesToFix"  
 $Folder = "C:\FilesToFix\"  
 $filesList = Get-ChildItem -Path $Folder -Filter *.csv | select fullname | ForEach-Object { $_.FullName }  
 foreach($file in $filesList)  
 {   
     $newfile = $file.Replace("FilestoFix","FilestoFix\fixed")  
     #Get lastwritetime for the filename   
     $LastWriteDateTime = Get-ItemPropertyValue -Path $file -Name LastWriteTime  
     #Import file and Export   
     Import-Csv $file | ForEach-Object {  
  if ($_.Field1.Trim() -eq '') {  
         $_.Field1 = '--'  
       }  
       if ($_.Field2.Trim() -eq '') {  
         $_.Field2 = '--'  
       }  
       $_  
  } | Export-Csv $newfile -NoTypeInformation -Force  
     Set-ItemProperty -Path $newfile -Name LastWriteTime -Value $LastWriteDateTime  
 }  

Powershell alert, once server is up and running

 We have got regular server patches every month. When the server is restarted, we have to keep checking whether server is up again. The below powershell script will be helpful, to let us know, once server is up and running.

1:  # Load assembly  
2:  [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")  
3:  $ComputerName = "FQDN of Server"  
4:  $ServerOnline = $false  
5:  while($ServerOnline -eq $false)  
6:  {  
7:    if(Test-Connection -BufferSize 32 -Count 1 -ComputerName $ComputerName -Quiet)  
8:    {  
9:     [System.Windows.Forms.Messagebox]::Show("The server $computerName is up now");  
10:     $serverOnline = $true;  
11:     break;  
12:    }  
13:    else  
14:    {  
15:      [System.Windows.Forms.Messagebox]::Show("The server $computerName is offline now");  
16:      Start-Sleep -Seconds 5  
17:    }  
18:  }  

Copy web.config between two web servers

We are having load balancing of web portal, in two different web servers. The web.config has to be same between web portal. If one of them is changed, we want to copy the same to another server. Below script can be used to do the same:

I have put local path just for reference. UNC path can be used in place of local path. 

1:  $strSourceFile = "C:\\dev\\wv02\\test.txt"  
2:  $strDestFile = "C:\\dev\\wv01\\test.txt"  
3:  if ((Get-ChildItem $strSourceFile).LastWriteTimeUtc -gt (Get-ChildItem $strDestFile).LastWriteTimeUtc)  
4:  {  
5:    write-host "$($strSourceFile) is latest than $($strDestFile)"  
6:    copy-item -Path $strSourceFile -Destination $strDestFile  
7:  }  
8:  else  
9:  {  
10:     write-host "$($strDestFile) is latest than $($strSourceFile)"  
11:     copy-item -Path $strDestFile -Destination $strSourceFile  
12:  }  

Sending Email in Powershell

Below script can be used to send email from Powershell, for audit report for yesterday. 

1:  $yesterday = (get-date).AddDays(-1).Date.ToString("yyyy_MM_dd")  
2:  $yesterdayMailSubject = (get-date).AddDays(-1).Date.ToString("yyyy-MM-dd")  
3:  $From = "test@email.com"  
4:  $To ="test@email.com"  
5:  $Cc = "test@email.com"  
6:  $Attachment = "C:\temp\Audit_$yesterday.csv"  
7:  $Subject = " DB Server Audit for Day: $yesterdayMailSubject "  
8:  $Body = "<h2>Please see the audit report attached file</h2><br><br>"  
9:  $SMTPServer = "SMTPServerName"  
10:  $SMTPPort = "587" #SMTPPort  
11:  Send-MailMessage -From $From -to $To -Cc $Cc -Subject "test" -Usessl -Body "test" -SmtpServer $SMTPServer -Port $SMTPPort -Credential $From -Verbose -Debug #-Attachments $Attachment  

How to create covering Index to fix performance Issue

We had a performance issue, where we had to create  covering index to solve the problem, as we were querying many columns in the table in a single query. 

Problem Statement:

we are having a table with 10 columns. We have 9 columns in a query, for getting clustering key (identity column here). 

1:  SELECT IdentityValue FROM TableWithIdentity   
2:  WHERE col1 = @col1 , col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5, col6 = @col6, col7 = @col7, col8 = @col8, col9 = @col9  

It is a SQL Server 2012 database. We had to create a covering index with 10 columns. How do I decide the order of columns in the covering index for efficient index ?

Solution Adopted:

1. Get the statistics names associated with table columns. 

1:  SELECT s.stats_id StatsID,  
2:   s.name StatsName,  
3:   sc.stats_column_id StatsColID,  
4:   c.name ColumnName  
5:  FROM sys.stats s  
6:   INNER JOIN sys.stats_columns sc  
7:    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
8:   INNER JOIN sys.columns c  
9:    ON sc.object_id = c.object_id AND sc.column_id = c.column_id  
10:  WHERE OBJECT_NAME(s.object_id) = 'TableWithIdentity'  
11:  ORDER BY s.stats_id, sc.column_id;  

2. Get the statistics for each column and note down the density of each column in the first result of the SHOW_STATISTICS. 

1:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName1');  
2:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName2');  
3:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column3Statistics');  
4:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column4Statistics');  
5:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column5Statistics');  
6:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumn6Statistics');  
7:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column7Statistics');  
8:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column8Statistics');  
9:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column9Statistics');  

3. Now, we know the density of each column. We have to choose the column with least density (meaning more unique column) and followed the next least density etc. 

0.00	column1
0.00	column2
0.01	column3
0.10	column4
0.33	column5
2.09	column6
3.09	column7
5.55	column8
6.46	column9

4. Now, we created covering index based on the uniqueness of the columns order

1:  CREATE INDEX IDX_TableWithIndentity_covering on dbo.TableWithIdentity(column1, column2,column3, column4, column5, column6, column7, column8, column9)   
2:  WITH  
3:  (  
4:  SORT_IN_TEMPDB = ON,  
5:  DROP_EXISTING = OFF  
6:  )  
7:  ON [PRIMARY]  

5. Now, the index is very helpful for fixing the performance Issue.

Getting detailed Performance issue metrics using sp_whoisactive

 

The below sp_whoisactive query can be executed to get detailed performance metrics for slowly running query, which can be later analyzed for troubleshooting performance issue

1:  EXEC sp_WhoIsActive @get_plans = 1, @get_locks = 1, @get_task_info = 2, @show_system_spids =1,  
2:  @show_own_spid =1, @show_sleeping_spids = 1  

Getting SQL Server Instance Services Information

Below query can be run to get SQL Server instance services information:

1:  SELECT DSS.servicename,  
2:  DSS.startup_type_desc,  
3:  DSS.status_desc,  
4:  DSS.last_startup_time,  
5:  DSS.service_account,  
6:  DSS.is_clustered,  
7:  DSS.cluster_nodename,  
8:  DSS.filename,  
9:  DSS.startup_type,  
10:  DSS.status,  
11:  DSS.process_id  
12:  FROM sys.dm_server_services AS DSS;  

Steps to follow for renaming Stored Procedures

There are two approaches to rename stored procedure. Dropping and Recreating Procedure : The problem is it will lead to losing of permissions. sp_rename of Procedure : Permissions will remain intact. The stored procedure will be renamed. But, sys.sql_modules will still be having the old definition. I prefer the second approach. I followed the below steps:

1. Have copy of the stored Procedure content Rename the stored procedure

1:  EXEC sp_rename 'dbo.OldStoredProcedureName','NewStoredProcedureName'  
2:  GO  

2. ALTER PROCEDURE to have the modified code of the procedure in the sys.sql_modules

1:  ALTER PROCEDURE dbo.NewStoredProcedureName  

3. Now, Stored procedure name is also updated and code is refreshed in sys.sql_modules and permissions are also intact.

Counting of files containing keyword in Powershell

When we want to see how many files are using a specific keyword, we can use below powershell script to do this. This will be helpful if we want to estimate the amount of code changes needed for a keyword change.

1:  set-location c:\dev\codefiles  
2:  $count = 0  
3:  $files = Get-ChildItem -Recurse -File `  
4:  #if we want to filter the files based on date range  
5:  `#|   Where-Object { $_.CreationTime -ge "12/13/2017" -and $_.CreationTime -le "12/29/2017" } `  
6:   ForEach($file in $files)  
7:   {  
8:     if ( (Get-Content $file -Raw).Contains("shipsite=`"KeywordToFind`""))  
9:     {  
10:      write-host $file; $count++;  
11:     }   
12:   }  
13:   Write-Host "count of files containing word:$count  

Loading CSV file to SQL Server table

Loading CSV file to database using BULK INSERT

Below are the step by step approach to easily load data from CSV file into SQL Server database table.

1. First create the table in the database corresponding to CSV file

CREATE TABLE NameList
(
FirstName varchar(40),
LastName varchar(40)
)

2. Now, generate format file for the above table using BCP Format option

C:\Users\venkat>BCP master.dbo.NameList format nul 
-x -S ServerName -c -f c:\dev\NameListFormat.xml -t, -T

3. Now, load data into the table using BULK INSERT

TRUNCATE TABLE dbo.NameList; -- (for testing)
BULK INSERT dbo.NameList
FROM 'c:\dev\NameList.csv'
WITH (FORMATFILE = 'C:\dev\NameListFormat.xml');
GO

4. Now, data is successfully loaded from CSV to SQL Server table.

Getting All SQL Agent Job Statuses

We can use below script to get status of all SQL Agent jobs, whether they are running or not. If they are running, we will get how many steps are there, what is the currently running step and elapsed time.


SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Never ran'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus',
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then js.StepCount else null end As TotalNumberOfSteps,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then ISNULL(sja.last_executed_step_id+1,js.StepCount) else null end as currentlyExecutingStep,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then datediff(minute, sja.run_requested_date, getdate()) ELSE NULL end as ElapsedTime
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
CROSS APPLY (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps as js WHERE js.job_id = sj.job_id) as js(StepCount)
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
ORDER BY RunStatus desc



Recursively look for files containing a keyword using Powershell

To recursively look for files containing a keyword in the code base, below Powershell script can be used.

set-location "CodeBaseFolder" 

Get-ChildItem -Recurse -File | Select-String  -Pattern "PatternToLook" | Select path, LineNumber, Line | Export-Csv C:\OutputFolder\KeywordPresence_InCodeBase.csv -NoTypeInformation

Executing SSIS Packages in SSIS Catalog

We can execute SSIS packages deployed in SSIS Catalog using stored procedure based approach as given below:

DECLARE @execution_id INT

EXEC [SSISDB].[catalog].[create_execution]

@package_name = N'Package.dtsx',

@execution_id = @execution_id OUTPUT,

@folder_name = N'FolderName',

@project_name = N'ProjectName',

@use32bitruntime = False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@execution_id,

@object_type = 30,

@parameter_name = N'FolderName',

@parameter_value = N'ParameterValue';

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@execution_id,

@object_type = 30,

@parameter_name = N'EnvironmentPath',

@parameter_value = N'EnvironmentValue';

exec catalog.set_execution_parameter_value  @execution_id, 50, 'DUMP_ON_ERROR',1

exec catalog.set_execution_parameter_value  @execution_id, 50, 'LOGGING_LEVEL',3

EXEC [SSISDB].[catalog].[start_execution] @execution_id;

Once we execute the package, we can check the status of execution using the below approach:

DECLARE @status AS BIGINT = 1;

WHILE(@status = 1 OR @status = 2 OR @status = 5 OR @status= 8)

BEGIN

PRINT @status

PRINT 'waiting 5 seconds for Package to finish'

WAITFOR DELAY '00:00:5';

SET @status = (SELECT [Status] FROM SSISDB.[catalog].[executions]

        WHERE execution_id = @execution_id);

END

If we want to execute the SSIS package using commandline, we can take the below approach:

dtexec.exe /isserver "\SSISDB\FolderName\ProjectName\Package.dtsx" /server "ServerName" /parameter $Package::FolderName;"ParameterValue" /parameter $Package::Sleep(Int32);60 /parameter $Package::MaxRun(Int32);300 /parameter $Package::EnvironmentPath;"EnvironmentValue"