Generate Managed Identity Using Powershell for Azure DataFactory

Open the powershell in the azure. (Select the correct subscription and create storage location for powershell or select an existing storage account)  

Run the below query with correct value inside double quote: 

Set-AzDataFactoryV2 -ResourceGroupName "MYResourceGroup" -Name "mydatafactory" -Location "UK South"

Press enter, and you'll be prompted to confirm. Type Y and press Enter.

You should see output like below:

Confirm

A data factory with the name mydatafactory in the resource group MYResourceGroup exists.

Continuing execution may overwrite the existing one.

Are you sure you want to continue?

[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y

DataFactoryName   : mydatafactory

DataFactoryId     : /subscriptions/e01105ad-822a-447a-2dd0-43124d16223l/resourceGroups/MYResourceGroup/providers/Microsoft.DataFactory/factories/mydatafactory

ResourceGroupName : MYResourceGroup

Location          : uksouth

Tags              : {}

Identity          : Microsoft.Azure.Management.DataFactory.Models.FactoryIdentity

ProvisioningState : Succeeded

RepoConfiguration :

GlobalParameters  :

For detail information read: Managed identity for Data Factory - Azure Data Factory | Microsoft Docs

Done!

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>