How to make some data in Azure Data Factory?

Question

I'm brand new to Azure Data Factory (ADF). I have Windows 11.

I'm doing a video tutorial on ADF on Udemy. I created an account on Azure Data Factory portal. The Udemy tutorial was created in 2022. The tutorial requires grabbing data from my local drive (a CSV file they provide) and loading it into a dataset. And this process requires a Self-Hosted Integration Runtime (SHIR) installed on my laptop to talk to the ADF website.

I have installed my SHIR on my laptop using the key from the ADF website. However in 2023 the SHIR no longer allows local file access by default, and a Microsoft site provides some Powershell scripts. The first Powershell cmdlet doesn't work and I cannot find the cmdlet specified anywhere in c:\program files\Microsoft Integration Runtime\5.0\. I'm using the instructions here https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory. I run a Powershell with Admin credentials. The first command Set-AzDataFactoryV2IntegrationRuntime is not found on my path nor in the SHIR directory I mention above.

Using the utility Everything I searched my whole C: drive for Set-AZDatafactory* and nothing appears so it wasn't installed with the SHIR.

So, to bypass any SHIR issues, how can I load an ADF dataset with test data, perhaps with SQL commands?

I would need detailed steps to do this since I don't know ADF at all.

Thank you so much!

Answer 1

You can set self-hosted integration runtime in ADF using below PowerShell script

Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName <resourceGroupName> -DataFactoryName <ADFName> -Name <SHIRName> -Type SelfHosted -Description "selfhosted IR description"

It will create SHIR in ADF studio as mentioned below:

As per this you have to download SHIR manually. It won't installed using above command.

After the installation of SHIR you need authentication key to connect with created SHIR using below PowerShell script:

Get-AzDataFactoryV2IntegrationRuntimeKey -ResourceGroupName <resourceGroupName> -DataFactoryName <ADFName> -Name <SHIRName>

Provide the authentication key in SHIR and register as mention below:

Create a file system linked service using SHIR and create the required dataset using the linked service as mentioned below:

Create dataset with required format. Create Azure SQL database linked service and create SQL database data set using it. Perform copy activity with file system as source and Sal database as sink in ADF pipeline to copy data from on-premises to Azure SQL database.

You can follow below pipeline JSON for your reference:

{
    "name": "pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "FileServerReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "sink": {
                        "type": "AzureSqlSink",
                        "writeBehavior": "insert",
                        "sqlWriterUseTableLock": false,
                        "tableOption": "autoCreate",
                        "disableMetricsCollection": false
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "typeConversion": true,
                        "typeConversionSettings": {
                            "allowDataTruncation": true,
                            "treatBooleanAsNumber": false
                        }
                    }
                },
                "inputs": [
                    {
                        "referenceName": "DelimitedText1",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "AzureSqlTable1",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "annotations": []
    }
}