none
Copying the File name into the destination table

    Question

  • Hi there,

    We are copying lots of files from a blob store to an azure SQL DB

    Is it possible to Add the files names to a column in the destination DB so you know where the records have come from?

    Thanks in advance

    Debbie


    Debbie

    Friday, October 26, 2018 2:45 PM

Answers

  • Hi, 

    You can do this with C# code. If you are using adf for data loading then you can write C# code something like below using adf custom activity.

    System.IO

    string fileName = @"C:\mydir\myfile.ext";
    string result;

    result = Path.GetFileName(fileName);


    Cheers,

    Monday, October 29, 2018 9:07 AM
  • Hi Debbie,

    There isn't a direct way to achieve the goal. You could firstly copy file to sql table, after the copy succeeds, pass the source filename as the parameter to a stored procedure activity, which's associated with a stored procedure script in sql database to insert the filename into sql table.Thanks.

    • Marked as answer by Debbie Edwards Tuesday, November 6, 2018 9:58 AM
    Tuesday, October 30, 2018 1:43 AM

All replies

  • Hi

    Can you please share what you have tried so far? Where exactly are you getting stuck?

    Regards,

    Alberto Vega

    Friday, October 26, 2018 7:50 PM
  • Maybe you could take a look at this post.

    The second answer provides  lots of related questions. 

    Monday, October 29, 2018 8:24 AM
  • Hi, 

    You can do this with C# code. If you are using adf for data loading then you can write C# code something like below using adf custom activity.

    System.IO

    string fileName = @"C:\mydir\myfile.ext";
    string result;

    result = Path.GetFileName(fileName);


    Cheers,

    Monday, October 29, 2018 9:07 AM
  • Fantastic. We haven't tried anything yet but we can certainly have a go at this.

    Thanks


    Debbie

    Monday, October 29, 2018 11:17 AM
  • Hi Debbie,

    There isn't a direct way to achieve the goal. You could firstly copy file to sql table, after the copy succeeds, pass the source filename as the parameter to a stored procedure activity, which's associated with a stored procedure script in sql database to insert the filename into sql table.Thanks.

    • Marked as answer by Debbie Edwards Tuesday, November 6, 2018 9:58 AM
    Tuesday, October 30, 2018 1:43 AM
  • That makes sense. Thank you

    Debbie

    Tuesday, November 6, 2018 9:58 AM