Create a batch file to copy/move files from network folder

Create a batch file (.bat) and paste the below code:

 @echo off

set DAYS=7

robocopy \\192.168.0.30\sanjeebapp\data\in E:\sanjeebapp\data\in\ /mov /maxage:%DAYS%

exit /b

Note: Change the source folder and destination folder as needed. /mov deletes the file from the source location . /maxage ignores the file which is older than DAYS. In this example, the files older than 7 days are not copied. 

Remove /mov from the script if you do not want to delete files in the source folder. 

 

Create Build/Release Pipeline for SSIS in Azure SQL server

Create a new pipeline in devops.

Select the source:

Select an empty template

Select Get Sources in the left panel:

Now add a new agent job to Build SSIS. Click the + icon.

Search for SSS job. (if it's not already installed, then first install it from the marketplace)

Add SSIS build and then add SSIS Deploy 

Once you add these two jobs, your screen should look like this:

Now Select Build SSIS:

in the Project Path field, select the directory where .sln file is located. 

Leave the rest of the fields as default.

Now Select Deploy SSIS job to configure deploying settings 

add source path = $(Build.ArtifactStagingDirectory) (i.e. output path value from Build SSIS job-Previous step).

select destination type = SSISDB.

Destination Server = realdatabaseserver01.database.windows.net

Destination Path = /SSISDB/masterdatahub

Authentication type = SQL Server Authentication (you can choose any other option as per your need)

Click on the Triggers tab and configure Continuous Integration. 

Now Save and Queue the Pipeline. 

Once released you will see the ssis package under masterdatahub folder

That's everything for Azure devops continuous integration/ continuous deployment process for releasing ssis project to azure sql server. Good luck!

How to modify Login in Azure SQL server

 for example, the login name is 'mysqluserlogin'

1) Enable the disabled login:

ALTER LOGIN mysqluserlogin ENABLE;

2) Change the login password:

ALTER LOGIN mysqluserlogin WITH PASSWORD = 'thisismynewpassword*&£1';

3) Change the login name:

ALTER LOGIN mysqluserlogin WITH NAME = mysqlusernewlogin;

How to create XSD schema from MSSQL Database table

 You'll need Microsoft SQL Server Management Studio to run the below query. 

Select the database where you want to run the query against. 

Open a new query window and run the below command 

    DECLARE @SanjeebXsdSchema xml

SET @SanjeebXsdSchema = (SELECT top 1 * FROM [dbo].[BOM_DW_Extract] FOR XML AUTO, ELEMENTS, XMLSCHEMA('sanjeebXsdSchema'))

SELECT @SanjeebXsdSchema

Result:

<xsd:schema xmlns:schema="sanjeebXsdSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="sanjeebXsdSchema" elementFormDefault="qualified">

  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

  <xsd:element name="dbo.BOM_DW_Extract">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="CompanyCode" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ParentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentSequence" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentQuantity" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="Reference" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="BomType" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="StartDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="EndDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ModifiedDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

How To create SSIS user in Azure SQL server

I've assumed you have already configured domain in azure and have created a domain user.

Create Application in app registration  

First of all Create an app registration [myssissqlserver01]

Set up Active directory Admin in sql server

Now Go to the sql server (not the database) and on settings menu, click Active directory Admin

Click Set Admin and add your domain account. (in case you don't have domain configured , Configure a new domain and create a domain user first)

Connect to the database using MS Sql server management studio

Once admin is set , Open MS Sql server management studio and connect to the database.

Since we have added user to active directory admin, the user will have access to master database. So we can choose default database to connect, 

Create a user in ssis and add to a suitable role

Create a new query window selecting SSISDB database

execute the below queries. Note that the user name should be identical to the Application name created in App registration section . [ myssissqlserver01]

create user [myssissqlserver01] from external provider;

exec sp_addrolemember [db_owner],[myssissqlserver01];

How to find first weekday in the month in MSSQL

declare @n datetime = dateadd(mm,0,getDate())

   declare @FirstWeekDayOfMonth int

   SELECT @FirstWeekDayOfMonth= CASE

         WHEN Datename(dw, Dateadd(dd, -Datepart(dd, @n) + 1, @n)) = 'Saturday' THEN Day(Dateadd(dd, -Datepart(dd, @n) + 3, @n))

         WHEN Datename(dw, Dateadd(dd, -Datepart(dd, @n) + 1, @n)) = 'Sunday' THEN Day(Dateadd(dd, -Datepart(dd, @n) + 2, @n))

         ELSE Day(Dateadd(dd, -Datepart(dd, @n) + 1, @n))

       END 

   select @FirstWeekDayOfMonth

Note: to find out first day of next month , change  first line to dateadd(mm,1,getDate()) 

1st day of next N month , change the first line to dateadd(mm,n,getDate())

Unit test c#: Create Fake Httpcontext to bypass Null exception for User object

 Microsoft Visual studio Test Framework

Requirement: Nuget package Moq 4.13.0.0 (or later)

Settings in visual studio: Menu->Tools->Options ->Debugging->General

Tick "Use Managed Compatibility Mode"

Code: (copied from stackoverflow)

Step 1: 

Create a new class FakeHttpContext and paste the below code. Change the Returns value to whatever user name you need.

public static class FakeHttpContext

    {

        public static void SetFakeContext(this Controller controller)

        {

            var httpContext = MakeFakeContext();

            ControllerContext context =

            new ControllerContext(

            new RequestContext(httpContext,

            new RouteData()), controller);

            controller.ControllerContext = context;

        }

        private static HttpContextBase MakeFakeContext()

        {

            var context = new Mock<HttpContextBase>();

            var request = new Mock<HttpRequestBase>();

            var response = new Mock<HttpResponseBase>();

            var session = new Mock<HttpSessionStateBase>();

            var server = new Mock<HttpServerUtilityBase>();

            var user = new Mock<IPrincipal>();

            var identity = new Mock<IIdentity>();

            context.Setup(c => c.Request).Returns(request.Object);

            context.Setup(c => c.Response).Returns(response.Object);

            context.Setup(c => c.Session).Returns(session.Object);

            context.Setup(c => c.Server).Returns(server.Object);

            context.Setup(c => c.User).Returns(user.Object);

            user.Setup(c => c.Identity).Returns(identity.Object);

            identity.Setup(i => i.IsAuthenticated).Returns(true);

            identity.Setup(i => i.Name).Returns("sanjeeb");

            return context.Object;

        }

    }

Step 2: 

Initialize the Fakehttpcontext in test initialize function. 

FakeHttpContext.SetFakeContext(usersController);

Accessing SMB share from Raspberry pi (or linux)

 Install smbclient :

sudo apt update 

sudo apt install smbclient

List the share items in the server: 

smbclient -L host

for example: smbclient -L 192.168.0.30

you need to enter password.then you'll see the result like this:

Domain=[WORKGROUP] OS=[Windows 6.1] Server=[Samba 4.9.5-Debian]

 Sharename       Type      Comment
 ---------       ----      -------
 print$          Disk      Printer Drivers
 RaspberrypiShare Disk      
 IPC$            IPC       IPC Service (Samba 4.9.5-Debian)
 pi              Disk      Home Directories
Domain=[WORKGROUP] OS=[Windows 6.1] Server=[Samba 4.9.5-Debian]

 Server               Comment
 ---------            -------
 RASPBERRYPI          Samba 4.9.5-Debian

 Workgroup            Master
 ---------            ------- 

To browse Shared folder :

smbclient \\\\192.168.0.30\\RaspberrypiShare pa$$w0rd

Temperature/CPU/Memory info of Raspberry Pi in console

 Open the console/terminal in the desktop or

Connect to the raspberry pi via ssh and type in the below command


For temperature:
/opt/vc/bin/vcgencmd measure_temp

For CPU info:
cat /proc/cpuinfo

For Memory info:
cat /proc/meminfo

Add Python(or Java or anything) to Environment variables

After installation of python in windows 10, python is not added to environment variable by default. If you want to use cmd for python, then python installed path must be configured in the environment variable. 

When you type python in cmd, you'll see the below message:

Follow the below steps to add python to environment:

1) Open System in Control panel

2) Click Advanced system settings

3)Click Environment Variables

4) Select Path in User Variables and click edit

5) Either create new and type in the path of Python installed location or Browse the folder using browse button in the above screenshot.

6) Click OK and close the Environment variables settings window and all other opened windows. 

7) Start a new command prompt and type python to test. 

Done!