Below is example PowerShell code to create a directory structure from file creation dates.
A use case for this code might be a need to organize a collection of files stored on an SFTP server or NAS. The code can be modified to loop through the files putting each one into sub directories corresponding to nested folders organized into Year\Month\Day folders.
To use the logic first create a test folder on your computer. Then copy the logic below to a text file editor and find and replace the following with reference to your test folder location:
Then save the code below as a PowerShell file called “DirFromDate.ps1” into your test folder.
To then run the file open up PowerShell and paste the following line:
Hit return and then enter the letter A if requested.
Then enter the following replacing “YourTestDirectory” with your actual test directory location:
<#.SYNOPSIS <A brief description of the script>.DESCRIPTION <A detailed description of the script>.PARAMETER <paramName> <Description of script parameter>.EXAMPLE <An example of using the script>#>#FIND AND REPLACE THE FOLLOWING WITH A DIRECTORY
#How Run In Powershell#Open Powershell terminal and run the folling line:#Set-ExecutionPolicy RemoteSigned#Type A and press Enter (if applicable).#Run the below including "&" at start of line#& "C:\YourTestDirectory\DirFromDate.ps1"#VARIABLES#Define folder variables$workingFolder = "C:\YourTestDirectory\"#Define file variable$testFile = "\ThisIsATestFile.txt"$testFile = $workingFolder + $testFile################################################(!!DO NOT ALTER SCRIPT PASSED THIS POINT!!)################################################Write variables to terminalWrite-Host"Filepath: $workingFolder";#Create test FileNew-Item$testFile#Define source directory variable$files = Get-ChildItem -Path $workingFolder#3 steps: move raw files to azure, zip file, move zipped file to azureforeach ($filein$files)
#FileNameWrite-Host"File: $file";#Get file creation date from file name$fileDate = $file.CreationTime.ToString("ddMMyyyy")
#Get file creation date (last time data was written to file) from file name#$fileDate = $file.LastWriteTime.ToString("ddMMyyyy")Write-Host"Filedate: $fileDate";$fileDate = $fileDate.Substring($fileDate.Length - 4) + "\" + $fileDate.Substring(3, 2) + "\" + $fileDate.Substring(0, 2)
Write-Host"Filedate: $fileDate";$DirectoryStructure = $workingFolder + "\" + $fileDateWrite-Host"DirStructure: $DirectoryStructure";
md $DirectoryStructureMove-Item -Path $testFile -Destination $DirectoryStructure
The script below will, providing the login you are using has adequate permissions, return the schema permissions a user has.
Commented out at the end of the script are examples of the types of permission you can assign, again providing the login you are using has adequate permissions.
SELECT class_desc AS ClassDescription
,state_desc AS StateDescription
,permission_name AS PermissionName
,SCHEMA_NAME(major_id) AS SchemaName
,USER_NAME(grantee_principal_id) AS UserName
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin ON PERM.major_ID = Prin.principal_id
AND class_desc ='SCHEMA'
) AS schemaPermissions
WHERE1=1/*Uncomment below to check permissions on a specific schema and/or specific user*/-- AND SchemaName = 'dbo'-- AND UserName = 'SomeGuy'ORDERBY UserName ASC
,SchemaName ASCGO/*--Grant schema permission examplesGRANT SELECT ON SCHEMA::dbo TO SomeGuy;GRANT UPDATE ON SCHEMA::dbo TO SomeGuy;GRANT ALTER ON SCHEMA::dbo TO SomeGuy;GRANT DELETE ON SCHEMA::dbo TO SomeGuy;*/
Below is a script you can modify to create a Login for an Azure SQL Server instance along with Users from that Login within databases.
To use the script don’t execute it all at once.
Execute the script in stages:
Section 1 & 2 can be executed together.
Section 3 requires you to switch to the target database.
Section 4 relates to limiting data access to a particular role or schema.
/*Find & replacethe following with a login name and user name*//*ServerLoginDbUser*//*1: Create SQL Login on master database (connect with admin account to master database)*/CREATE LOGIN ServerLogin
WITH PASSWORD ='<strong_password>';
/*2: Create SQL User on the master database (this is necessary for login attempt to the <default> database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.) */CREATEUSER DbUser
FROM LOGIN ServerLogin;
/***************************//*SWITCH TO TARGET DATABASE*//***************************//*3: Create SQL User on the user database (connect with admin account to user database)*/CREATEUSER DbUser
FROM LOGIN ServerLogin;
/*************************//*SECURITY CONSIDERATIONS*//*************************//*Set up role example*//*4. Grant permissions to the user by assigning them to a database role*/ALTERROLE db_datareader ADD MEMBER DbUser;
/*Limit access to schema*//*Or instead of step four you can use the below:*/GRANTSELECTONSCHEMA::schemaName
Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.
To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.
If you’re worried about messing anything up then back up the table before running the script!
Update/replace or remove an email address:
/*To replace an email address with another email address:Find and replace the following email addresses (Ctrl+H) with the new email address*//*Email address to update/replace:replaceSomeGuy@someCompany.comEmail address replacement:newGuy@someCompany.com*/UPDATE [dbo].[Subscriptions]
SET ExtensionSettings =REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'replaceSomeGuy@someCompany.com', 'newGuy@someCompany.com')
WHERE CHARINDEX('replaceSomeGuy@someCompany.com', ExtensionSettings) <>0--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
/*To remove an email address:NOTE:Run both of the following scripts as an email address may or may not end with ";"Find and replace the following email address (Ctrl+H)*//*Email address to removeremoveSomeGuy@otherCompany.com*/UPDATE [dbo].[Subscriptions]
SET ExtensionSettings =REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com;', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com;', ExtensionSettings) <>0--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
SET ExtensionSettings =REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com', ExtensionSettings) <>0--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
Typically most pipeline development use cases can be handled directly within Data Factory through the Azure Web Portal. However where the line can get blurred sometimes between working in the cloud and working locally is with DevOps GIT.
If a GIT based deployment gets tangled there is an expectation you will be able to work through the desktop interface for GIT or worse fall back to using command line.
This is necessary because before a GIT pull request can complete, any conflicts with the target branch must be resolved and this usually involves issuing a few commands to put the matter right. The options for resolving conflicts through the web portal by default are limited to nonexistent which is at odds with the very high level, low code approach of developing pipelines in Data Factory.
Luckily if a merge conflict occurs there is an extension you can try.
A conflict might occur because the master branch is no longer in sync with the development branch for example i.e. the master branch was changed after a development branch was created from it. When a pull request is created this may throw a merge conflict error blocking the merge from proceeding. Without resorting to code the extension above will allow you to choose between the source and target branch and specify which has the correct file version.
Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.
Note: Pass in Longitude and Latitude values in that order.
/*Demo of geo data*/DECLARE@g GEOGRAPHY;
SET@g= GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);
/*Geography data is in binary format*/SELECT@gAS'GeoBinaryFormat';
/*Convert binary data to a string*/SELECT@g.ToString() AS'ConvertingDataToString';
/*Inserting geo data into Table*/CREATETABLE#GeoTest ([CoordinateLocation] [geography] NULL);
SELECT GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);
Time cannot be summed directly in T-SQL. In order to sum two times they first need to be assigned a date. When a time data type is cast as a datetime data type, as it does not have a date element, the value defaults to the date of 1900-01-01.
As T-SQL does have the functionality to sum datetime and as the date element will be the same only the time value will be summed. This functionality allows us to sum time.
Below is example T-SQL:
IF OBJECT_ID('tempdb..#TimeTable', 'U') ISNOTNULLBEGINDROPTABLE#TimeTable
/*demo of time converted to datetime*/SELECTCAST(TimeRecord AS DATETIME) AS DateTimeRecord
,CAST(DATEADD(MILLISECOND, SUM(DATEDIFF(MILLISECOND, 0, CAST(TimeRecord AS DATETIME))), 0) AS TIME(0)) AS SummedTime
This is a very common activity in the data world, i.e. there’s some data in a text string you need and the rest of the data in the string is just in your way. Some use cases might be you have a reference in a filename you need to extract, or you may need a snippet of data to create a composite key, or there’s an order number surrounded by other data that is not relevant to your needs etc.
The following is some simple T-SQL that will extract the data you want from a text string providing the data has specific delimiting characters on each side of it.
/*Delimiter variables, first and second position*/DECLARE@dfp ASCHAR(1);
SET@text='I want you to ;Extract this@ substring for me please.';
SELECTSUBSTRING(@text, (CHARINDEX(@dfp, @text) +1), (CHARINDEX(@dsp, @text) -2) - CHARINDEX(@dfp, @text) + Len(@dsp))
You might have heard the term right to repair being thrown around in recent years and thought little of it. Maybe you’re an engineering graduate and fixing something yourself is not only a piece of cake for you but also your idea of a good time. Or maybe you are good with your hands but it would be ludicrous to spend your hard earned weekend fixing something yourself. Especially if you can hand it over to the well reviewed local repair shop that will do a perfect repair for a fair price.
Unfortunately without right to repair legislation manufactures have the power to take away your option to do either. Manufactures can refuse to provide documentation as to how to fix or service a product. Through monopolistic control of the supply chain manufacturers can make it so that consumers need to return their broken product directly to them if they want them repaired. Often repairs carried out by the manufacturer are done at exorbitant prices making the prospect of repairing the product uneconomical incentivizing customers to just purchase the newer product model. Through tight control of the supply chain manufacturers can even force the end of life of a product by no longer manufacturing replacement parts and/or suing a company that does. Manufacturers have even gone as far as making it difficult for wholesalers and repair shops to buy and sell used parts in bulk and suing repair shops for using unlicensed parts. This means that a product that could be easily repaired gets thrown in the trash all because a cheap, new or used, component part is unavailable.
You still might be thinking that all of this doesn’t concern you. You might be the type of person that buys a new phone every year, whether it needs replacing or not, or if something breaks you’ve got the money to buy a new one. Why is this such a big deal? What if I told you it could literally be the difference between life and death.
Below is a video that highlights how not having right to repair legislation in place affects everyone in society. The video is a conversation between Louis Rossmann and an hospital employee discussing all the difficulties an operating table manufacturer has put it place to make their operating table irreparable. Instead of the hospital having the option of repairing an operating table for the price of a $1,800 dollar hydraulic pump the hospital will be forced to buy either a used operating table for approx. $20 K plus or a new table at $40 K plus. That’s the potential difference in the salary of a nurse for a year.
Clearly this is not an isolated problem only affecting people at an individual level leaving repairable consumer products broken. By not affording individuals the right to repair their own devices this translates to restrictions on organizations doing the same. At an individual level it’s a shady business practice at best but scaled up to the organizational level it can be downright morally reprehensible.
Rossmann is active campaigner and advocate for right to repair legislation. The right to repair refers to government legislation that is intended to allow consumers the ability to repair and modify their own devices, where otherwise the manufacturer of such devices requires the consumer to use only their offered services.
If you would like to help Mr Rossmann in his fight to protect consumer right to repair in the state of New York please consider donating to his nonprofit called Repair Preservation Group Action Fund available through the link below.