CODEWITHSUNDEEP
azure-sql-databaseazure-data-factory
Erik hoeven
Erik hoeven

Reputation: 1600

Azure Data Factory Export SQL output to blobstorage

I am using Azure SQL Database with a physical table with the following structure:

create table ind_metadata_reportages.ind_datadictionary (
 code           nvarchar(250)
,name           nvarchar(250)
,EntiteitNaam   nvarchar(255)
,AttribuutNaam  nvarchar(250)
,Contstraint    nvarchar(250)
,DataType       nvarchar(250)
)

To generate a DDL from the table content, I use the following statement:

declare @table_name     varchar(255)
      , @attribute_name varchar(255)
      , @datatype       varchar(255)
      , @sql            nvarchar(max)
      , @previous_table varchar(255) = ''
      , @schema  varchar(255) = 'ind_logische_datamodel'
      , @ind_next int = 0

declare c_ddl cursor for
select entiteitnaam, attribuutnaam, datatype
from ind_metadata_reportages.ind_datadictionary

open c_ddl

fetch next from c_ddl into @table_name, @attribute_name, @datatype 

while @@fetch_status = 0
begin
    
    -- start van de tabel
    if @previous_table = '' and @ind_next = 0
    begin 
        set @previous_table = @table_name 
        set @sql = 'CREATE TABLE ' + @schema + '.' + @previous_table + '( 
        ' + @attribute_name +  ' ' + @datatype
        set @ind_next = 1
        
    end
    
    -- einde van de tabel
    if @previous_table != @table_name  and  @ind_next = 0
    set @sql =  @SQL + N' );
     
    CREATE TABLE ' + @schema + '.' + @previous_table + '( '     
    + @attribute_name +  ' ' + @datatype
    set @previous_table = @table_name
        
    if @previous_table = @table_name and @ind_next = 0 
    BEGIN 
        set @sql = @sql + N'
        , ' + @attribute_name +  ' ' + @datatype  
        set @ind_next = 1
    END
    
    set @ind_next = 0
    fetch next from c_ddl into @table_name, @attribute_name, @datatype 
end

print @sql

close c_ddl
deallocate c_ddl

I want the output which is printed in the end saved in a file on the Azure Blob Storage. Do you have an idea how I can solve this with Azure Data Factory?

Many thanks Erik

Upvotes: 0

Views: 771

Answers (1)

Frode
Frode

Reputation: 356

You can do this in multiple ways.

  1. Create a stored procedure around it, and return your print as dataset with one column.
  2. Create a temporary table in your database and save your output in int.

In ADF you do a normal copy function from your stored procedure or table to a file in your Blob Storage.

Upvotes: 1

Related Questions