Using GitHub Actions How to get the details of commit ID,Respository name,Branch,Commit date and Commit Author to Azure SQL Database(looking for A process flow how can i do it)

Question

Hi,

I have created one table in Azure SQL Database with columns ( Commit ID, Repository name, Branch, Commit date and Commit Author) I am trying to use GitHub Actions to pull the data from GitHub to Azure SQL .Can someone please help on how to get the details from GitHub Pipeline (Yaml File) and store it in Azure SQL Database.

Thanks in advance..!!

Answer 1

Hi @Pulipati Vedanth ,

Thanks for using Microsoft Q&A !!
You can use parameterized SQL Script Script file and pass the required values like RepositoryNames, Branch etc. to the script file using arguments: attribute and use SQLCMD scripting variable with -v as the argument value as shown below -

name: SQL for GitHub Actions  
  
on:  
 push:  
    branches: [ main ]  
 pull_request:  
    branches: [ main ]  
  
  
jobs:  
  build:  
   runs-on: windows-latest  
   steps:  
    - uses: actions/checkout@v1  
    - uses: azure/login@v1  
      with:  
        creds: ${<!-- -->{ secrets.AZURE_CREDENTIALS }}  
      
    - uses: azure/sql-action@v1  
      with:  
       server-name: 'servername.database.windows.net'  
       connection-string: ${<!-- -->{ secrets.AZURE_SQL_CONNECTION_STRING }}  
       sql-file: './test_sql_script.sql'  
       arguments: "-v ColValue1 = '${<!-- -->{ github.event.repository.name }}' ColValue2 = 'Test'"  
    # Azure logout   
    - name: logout  
      run: |  
           az logout  

Update the SQL Script file accordingly to consume the passed arguments like below -
INSERT INTO dbo.[project_table](RepositoryName,Branch) values($(ColValue1),$(ColValue2))

Result (Notice ColValue1 is replaced with GitHub Repository Name):-

SQL

I have passed two different values to the script in the above example but you can pass multiple arguments as per your requirement. Hope this helps.
Also, you need to look for GitHub environment variables or commands to get the commit date, author details etc. in GitHub repository so that you can pass it as arguments. Hope this helps !!

Please let me know if you have any questions !!

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.