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

Load data from one database to another database in ADLA

Currently in U-SQL, there is no support for backup and restore of databases. So, if you are having a production database and want to restore the data from production to development, you dont have easy mechanism to do it.

Below powershell script will be helpful to copy all data from production database to development database, provided both are having same schema.

Make sure that you are doing deployment in both development and production database and they are in same schema for tables.

The below powershell script generates the TRUNCATE TABLE & INSERT INTO script for all the production database tables. This genrerated script can be run as a job in ADLA development account. Replace the highlighted values with your details accordingly.  

Connect-AzureRmAccount -SubscriptionName "YOUR_SUBSCRIPTIONNAME"

$usqlScriptForTablesBackup = ""

$tables = @()

$databaseName = "PRODUCTIONDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "PRODUCTIONDB",$table.schema, $table.table

}

$databaseName = "DEVELOPMENTDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "sources",$table.schema, $table.table

}

$usqlScriptForTablesBackup | Out-File -FilePath "D:\backupscript.usql"

git creating a branch and setting upstream branch

If you are working on a new task and you want to create a user branch and push it to remote, you can do so, using below methods.

Assuming you are in develop branch

git checkout develop #Recommended to always point your branch to same commit as develop
git pull                       #Recommened to get the latest from remote
git branch users/venkat/performanceFix #Creating new branch for your needs
git checkout users/venkat/performanceFix #Checking out branch locally
git push origin users/venkat/performanceFix  #Pushing the branch to remote
git push --set-upstream origin users/venkat/germanyThresholdChanges #Setting upstream branch for  current branch as the newly created branch


or

git checkout -b users/venkat/performanceFix Origin/venkat/performanceFix #single step if the remote branch already exists

or

If the remote branch already exists and you want to set the remote branch as current branch upstream.

git branch users/venkat/performanceFix
git checkout users/venkat/performanceFix
git branch --set-upstream-to origin/users/venkat/performanceFix

Finding Deleted file content in git

I was searching for a deleted file content and wanted to see which commit, the file was deleted. Finally, was able to resolve the same using the below commands.

git log -p -- DeletedFilePath  #Here, p stands for patch mode   or

git rev-list -n 1 HEAD -- DeletedFilePath # Here, we are printing commits in reverse chronological way

After I found the commit, I went to VSO to search for the commit. We can also find the commit content using below git command.

git show