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++)}