Azure Data Factory Export SQL output to blobstorage

Question

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

marc_s · Accepted Answer · 2021-09-25 09:07:25Z

1

You can do this in multiple ways.

Create a stored procedure around it, and return your print as dataset with one column.
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.

edited Sep 25, 2021 at 9:07

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 4, 2021 at 9:11

Frode

3564 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erik hoeven Over a year ago

Thanks frode for the fast response! I already create a stored procedure and a pipeline which executes the stored procedure! I have already update the question with the pictures. But how can i pass that value from a column to a file in the Blob Storage ?

Frode Over a year ago

You can use a copy data activity to read from your table and copy it to the blob storage.

Collectives™ on Stack Overflow

Azure Data Factory Export SQL output to blobstorage

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related