Jalankan paket SSIS dengan aktivitas Prosedur Tersimpan

Artikel
07/24/2023

BERLAKU UNTUK:Azure Data Factory Azure Synapse Analytics

Tip

Cobalah Data Factory di Microsoft Fabric, solusi analitik all-in-one untuk perusahaan. Microsoft Fabric mencakup semuanya mulai dari pergerakan data hingga ilmu data, analitik real time, kecerdasan bisnis, dan pelaporan. Pelajari cara memulai uji coba baru secara gratis!

Artikel ini menjelaskan cara menjalankan paket SSIS di alur Azure Data Factory pr Alur Synapse dengan menggunakan aktivitas Stored Procedure.

Prasyarat

Database Azure SQL

Panduan dalam artikel ini menggunakan Azure SQL Database untuk menghosting katalog SSIS. Anda juga dapat menggunakan Azure SQL Managed Instance.

Data Factory

Anda akan memerlukan instans Azure Data Factory untuk menerapkan panduan ini. Jika belum diprovisikan, Anda dapat mengikuti langkah-langkah di Mulai Cepat: Membuat pabrik data menggunakan portal Azure dan Azure Data Factory Studio.

runtime integrasi Azure-SSIS

Terakhir, Anda juga akan memerlukan runtime integrasi Azure-SSIS jika tidak memilikinya dengan mengikuti petunjuk langkah demi langkah dalam Tutorial: Menyebarkan paket SSIS.

Membuat alur dengan aktivitas prosedur tersimpan

Dalam langkah ini, Anda menggunakan antarmuka pengguna Data Factory untuk membuat alur. Jika Anda belum menavigasi ke Azure Data Factory Studio, buka pabrik data Anda di Portal Azure dan klik tombol Buka Azure Data Factory Studio untuk membukanya.

Screenshot of the Azure Data Factory home page.

Berikutnya, Anda akan menambahkan aktivitas prosedur tersimpan ke alur baru dan mengonfigurasinya untuk menjalankan paket SSIS menggunakan prosedur tersimpan sp_executesql.

Di halaman beranda, klik Atur:
Di kotak alat Aktivitas, cari Prosedur tersimpan, dan seret-letakkan aktivitas Prosedur Tersimpan ke permukaan desainer alur.
Pilih aktivitas Prosedur tersimpan yang baru saja Anda tambahkan ke permukaan perancang, lalu tab Pengaturan, dan klik + Baru di samping Layanan tertaut. Anda membuat koneksi ke database di Azure SQL Database yang menghosting Katalog SSIS (database SSIDB).
Di jendela Layanan Tertaut Baru, lakukan langkah-langkah berikut:
1. Pilih Azure SQL Database untuk jenis.
2. Pilih AutoResolveIntegrationRuntime Default untuk tersambung ke Azure SQL Database yang menghosting database SSISDB.
3. Pilih Azure SQL Database yang menghosting database SSISDB untuk bidang Nama server.
4. Pilih SSISDB untuk Nama database.
5. Untuk Nama pengguna, masukkan nama pengguna yang memiliki akses ke database.
6. Untuk Kata sandi, masukkan kata sandi untuk pengguna.
7. Uji koneksi ke database dengan mengklik tombol Uji koneksi.
8. Simpan layanan tertaut dengan mengklik tombol Simpan.

Kembali di jendela properti pada tab Pengaturan, selesaikan langkah-langkah berikut:

Pilih Edit.
Untuk bidang Nama prosedur tersimpan, Masukkan sp_executesql.
Klik + Baru di bagian Parameter prosedur tersimpan.
Untuk nama parameter, masukkan stmt.
Untuk jenis parameter, masukkan String.

Untuk nilai parameter, masukkan kueri SQL berikut:

Dalam kueri SQL, tentukan nilai yang tepat untuk parameter folder_name, project_name, dan package_name.

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'<FOLDER name in SSIS Catalog>', @project_name=N'<PROJECT name in SSIS Catalog>', @package_name=N'<PACKAGE name>.dtsx', @use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 BEGIN SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) END

Azure SQL Database linked service

Untuk memvalidasi konfigurasi alur, pilih Validasi pada toolbar. Untuk menutup jendela Laporan Validasi Alur, klik >>.
Terbitkan pipeline ke Data Factory dengan mengklik tombol Terbitkan Semua.

Jalankan dan pantau alur

Di bagian ini, Anda memicu eksekusi alur dan kemudian memantaunya.

Untuk memicu eksekusi alur, klik Pemicu pada toolbar, dan klik Picu sekarang.
Di jendela Ekseskusi Alur, pilih Selesai.
Beralih ke tab Monitor di bagian kiri. Anda melihat eksekusi alur dan statusnya beserta informasi lain (seperti Waktu Mulai Eksekusi). Untuk me-refresh daftar, klik Refresh.
Klik tautan Lihat Eksekusi Aktivitas di kolom Tindakan. Anda hanya melihat satu eksekusi aktivitas karena alur hanya memiliki satu aktivitas (aktivitas prosedur tersimpan).
Anda dapat menjalankan kueri berikut terhadap database SSISDB di Azure SQL Database untuk memverifikasi bahwa paket tersebut dijalankan.
```
select * from catalog.executions
```

Catatan

Anda juga dapat membuat pemicu terjadwal untuk alur Anda sehingga alur berjalan sesuai jadwal (per jam atau harian, dll.). Misalnya, lihat Membuat pabrik data - Antarmuka pengguna Data Factory.

Azure PowerShell

Catatan

Sebaiknya Anda menggunakan modul Azure Az PowerShell untuk berinteraksi dengan Azure. Lihat Menginstal Azure PowerShell untuk memulai. Untuk mempelajari cara bermigrasi ke modul Az PowerShell, lihat Memigrasikan Azure PowerShell dari AzureRM ke Az.

Di bagian ini, Anda menggunakan Azure PowerShell untuk membuat alur Data Factory dengan aktivitas prosedur tersimpan yang memanggil paket SSIS.

Pasang modul Azure PowerShell terbaru dengan mengikuti petunjuk dalam Cara menginstal dan mengonfigurasi Azure PowerShell.

Membuat pabrik data

Anda dapat menggunakan pabrik data yang sama yang memiliki Azure-SSIS IR atau membuat pabrik data terpisah. Prosedur berikut ini menyediakan langkah-langkah untuk membuat data factory. Anda membuat alur dengan aktivitas prosedur tersimpan di pabrik data ini. Aktivitas prosedur tersimpan menjalankan prosedur tersimpan dalam database SSISDB untuk menjalankan paket SSIS Anda.

Tentukan variabel untuk nama grup sumber daya yang Anda gunakan di perintah PowerShell nanti. Salin teks perintah berikut ke PowerShell, tentukan nama untuk grup sumber daya Azure dalam tanda kutip ganda, lalu jalankan perintah. Sebagai contoh: "adfrg".
```
$resourceGroupName = "ADFTutorialResourceGroup";
```
Jika grup sumber daya sudah ada, Anda mungkin tidak ingin menimpanya. Tetapkan nilai yang berbeda ke variabel $ResourceGroupName dan jalankan perintah lagi
Untuk membuat grup sumber daya Azure, jalankan perintah berikut:
```
$ResGrp = New-AzResourceGroup $resourceGroupName -location 'eastus'
```
Jika grup sumber daya sudah ada, Anda mungkin tidak ingin menimpanya. Tetapkan nilai yang berbeda ke variabel $ResourceGroupName dan jalankan perintah lagi.
Tentukan variabel untuk nama pabrik data.

Penting

Perbarui nama pabrik data menjadi nama yang unik secara global.
```
$DataFactoryName = "ADFTutorialFactory";
```
Untuk membuat pabrik data, jalankan cmdlet Set-AzDataFactoryV2 berikut ini, menggunakan properti Location dan ResourceGroupName dari variabel $ResGrp berikut:
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

Perhatikan poin berikut:

Nama pabrik data Azure harus bersifat unik secara global. Jika Anda menerima kesalahan berikut, ubah nama dan coba lagi.

The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.

Untuk membuat instans Data Factory, akun pengguna yang Anda gunakan untuk masuk ke Azure harus merupakan anggota dari peran kontributor atau pemilik, atau administrator dari langganan Azure.
Untuk daftar wilayah Azure tempat Data Factory saat ini tersedia, pilih wilayah yang menarik minat Anda pada halaman berikut, lalu perluas Analitik untuk menemukan Data Factory: Produk yang tersedia menurut wilayah. Penyimpanan data (Azure Storage, Azure SQL Database, dll.) dan komputasi (HDInsight, dll.) yang digunakan oleh pabrik data dapat berada di wilayah lain.

Buat layanan tertaut Azure SQL Database

Buat layanan tertaut untuk menautkan database yang menghosting katalog SSIS ke pabrik data Anda. Data Factory menggunakan informasi dalam layanan tertaut ini untuk terhubung ke database SSISDB, dan menjalankan prosedur yang disimpan untuk menjalankan paket SSIS.

Buat file JSON bernama AzureSqlDatabaseLinkedService.json folder C:\ADF\RunSSISPackage dengan konten berikut:

Penting

Ganti <servername>, <username>, dan <password> dengan nilai Azure SQL Database sebelum menyimpan file.

{
    "name": "AzureSqlDatabaseLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=SSISDB;User ID=<username>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
        }
    }
}

Di Azure PowerShell, beralihlah ke folder C:\ADF\RunSSISPackage.

Jalankan cmdlet Set-AzDataFactoryV2LinkedService untuk membuat layanan tertaut: AzureSqlDatabaseLinkedService.

Set-AzDataFactoryV2LinkedService -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -Name "AzureSqlDatabaseLinkedService" -File ".\AzureSqlDatabaseLinkedService.json"

Membuat alur dengan aktivitas prosedur tersimpan

Dalam langkah ini, Anda membuat alur dengan aktivitas prosedur tersimpan. Aktivitas ini meminta sp_executesql untuk menjalankan paket SSIS Anda.

Buat file JSON bernama RunSSISPackagePipeline.json dalam folder C:\ADF\RunSSISPackage dengan konten berikut:

Penting

Ganti <NAMA FOLDER>, <NAMA PROYEK>, <NAMA PAKET> dengan nama folder, proyek, dan paket dalam katalog SSIS sebelum menyimpan file.

{
    "name": "RunSSISPackagePipeline",
    "properties": {
        "activities": [
            {
                "name": "My SProc Activity",
                "description":"Runs an SSIS package",
                "type": "SqlServerStoredProcedure",
                "linkedServiceName": {
                    "referenceName": "AzureSqlDatabaseLinkedService",
                    "type": "LinkedServiceReference"
                },
                "typeProperties": {
                    "storedProcedureName": "sp_executesql",
                    "storedProcedureParameters": {
                        "stmt": {
                            "value": "DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'<FOLDER NAME>', @project_name=N'<PROJECT NAME>', @package_name=N'<PACKAGE NAME>', @use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 BEGIN SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) END"
                        }
                    }
                }
            }
        ]
    }
}

Untuk membuat alur: RunSSISPackagePipeline, Jalankan cmdlet Set-AzDataFactoryV2Pipeline.

$DFPipeLine = Set-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -Name "RunSSISPackagePipeline" -DefinitionFile ".\RunSSISPackagePipeline.json"

Berikut adalah output sampel:

PipelineName      : Adfv2QuickStartPipeline
ResourceGroupName : <resourceGroupName>
DataFactoryName   : <dataFactoryName>
Activities        : {CopyFromBlobToBlob}
Parameters        : {[inputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification], [outputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}

Membuat eksekusi alur

Gunakan cmdlet Invoke-AzDataFactoryV2Pipeline untuk menjalankan alur. Cmdlet menampilkan ID eksekusi alur untuk pemantauan di masa mendatang.

$RunId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -PipelineName $DFPipeLine.Name

Memantau eksekusi alur

Jalankan skrip PowerShell berikut ini untuk terus memeriksa status eksekusi alur hingga selesai menyalin data. Salin/tempel skrip berikut di jendela PowerShell, dan tekan ENTER.

while ($True) {
    $Run = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -PipelineRunId $RunId

    if ($Run) {
        if ($run.Status -ne 'InProgress') {
            Write-Output ("Pipeline run finished. The status is: " +  $Run.Status)
            $Run
            break
        }
        Write-Output  "Pipeline is running...status: InProgress"
    }

    Start-Sleep -Seconds 10
}

Membuat pemicu

Pada langkah sebelumnya, Anda menjalankan alur sesuai permintaan. Anda juga dapat membuat pemicu jadwal untuk menjalankan alur sesuai jadwal, seperti per jam atau harian.

Buat file JSON bernama MyTrigger.json dalam folder C:\ADF\RunSSISPackage dengan konten berikut:

{
    "properties": {
        "name": "MyTrigger",
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Hour",
                "interval": 1,
                "startTime": "2017-12-07T00:00:00-08:00",
                "endTime": "2017-12-08T00:00:00-08:00"
            }
        },
        "pipelines": [{
                "pipelineReference": {
                    "type": "PipelineReference",
                    "referenceName": "RunSSISPackagePipeline"
                },
                "parameters": {}
            }
        ]
    }
}

Di Azure PowerShell, beralihlah ke folder C:\ADF\RunSSISPackage.

Jalankan cmdlet Set-AzDataFactoryV2Trigger, yang membuat pemicu.

Set-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" -DefinitionFile ".\MyTrigger.json"

Secara default, pemicu dalam status berhenti. Mulai pemicu dengan menjalankan cmdlet Start-AzDataFactoryV2Trigger.

Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger"

Konfirmasikan bahwa pemicu dimulai dengan menjalankan cmdlet Get-AzDataFactoryV2Trigger.

Get-AzDataFactoryV2Trigger -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -Name "MyTrigger"

Jalankan perintah berikut setelah satu jam berikutnya. Misalnya, jika waktu saat ini adalah 15.25 UTC, jalankan perintah pada pukul 16.00 UTC.
```
Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"
```
Anda dapat menjalankan kueri berikut terhadap database SSISDB di Azure SQL Database untuk memverifikasi bahwa paket tersebut dijalankan.
```
select * from catalog.executions
```

Anda juga dapat memantau alur menggunakan portal Microsoft Azure. Untuk instruksi langkah demi langkah, lihat Memantau alur.

Jalankan paket SSIS dengan aktivitas Prosedur Tersimpan

Prasyarat

Database Azure SQL

Data Factory

runtime integrasi Azure-SSIS

Membuat alur dengan aktivitas prosedur tersimpan

Jalankan dan pantau alur

Azure PowerShell

Membuat pabrik data

Buat layanan tertaut Azure SQL Database

Membuat alur dengan aktivitas prosedur tersimpan

Membuat eksekusi alur

Memantau eksekusi alur

Membuat pemicu

Konten terkait

Sumber Daya Tambahan: