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"

Finding IP address details of SQL Server machine

The below query can be used get IP address details of SQL Server machine.

SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

Renaming Files Incrementally in a folder, using Powershell

 The below script can be used to rename files incrementally in a folder.

$path = "C:\Users\rvenk\Desktop\SBI Empanelment Docs"
$nr = 1
Get-ChildItem $path | ForEach-Object {$fullName = $_.FullName; $fileName = $_.Name; Rename-Item $fullName -NewName ("{0}_$filename" -f $nr++)}