Azure Functions - SQL DB Extract to Output Blob

Question

I make reference to the below solution that I found on Stack. It's pretty close to what I am attempting to do but after a week of trying various options I am no closer.

I am attempting to write an Azure Function that will extract all the data from Azure Database and output the results to Blob Storage..

Below is my attempt. After exhausting all others, admittedly I'm not great at C# so it's probably my shortcoming. Any help will be greatly appreciated.

Here is the Azure Function:

#r "System.Configuration"
#r "System.Data"

using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Text;
using System.IO;

public static void Run(
    [TimerTrigger("0 */30 * * * *")] TimerInfo myTimer, 
    [Blob("mycontainer/myblob.txt", FileAccess.Write)] out string OutputBlob, 
    TraceWriter log)
{

   SqlDataReader rdr = null;

   var str = ConfigurationManager.ConnectionStrings["sqldb_connection"].ConnectionString;

   using (var conn = new SqlConnection(str))
   {
        conn.Open();

        // Query Text
        var text = "SELECT FirstName, LastName FROM Kpi";

        using (var cmd = new SqlCommand(text, conn))
        {
            // Execute the command 
            rdr = cmd.ExecuteReader();

            var csv = new StringBuilder();

            while (rdr.Read())
            {
                // get the results of each column
                string FirstName = (string)rdr["FirstName"];
                string LastName = (string)rdr["LastName"];

                // create a string
                var newLine = string.Format("{0},{1}", FirstName, LastName);
                csv.AppendLine(newLine);
            }

            // How do I get the results to the outputBlob ??
            File.WriteAllText(outputBlob, csv.ToString());
        }
    }
}

Here is the error:

Microsoft.Azure.WebJobs.Host.FunctionInvocationException : Exception while executing function: Functions.TimerTriggerCSharp1 ---> Microsoft.CodeAnalysis.Scripting.CompilationErrorException : Script compilation failed. at async Microsoft.Azure.WebJobs.Script.Description.DotNetFunctionInvoker.CreateFunctionTarget(CancellationToken cancellationToken) at C:\projects\azure-webjobs-sdk-script\src\WebJobs.Script\Description\DotNet\DotNetFunctionInvoker.cs : 340 at async

Error Message screenshot

Solution Found on Stack Overflow

Apologies, here is the error below Exception while executing function: Functions.TimerTriggerCSharp1 Microsoft.Azure.WebJobs.Host.FunctionInvocationException : Exception while executing function: Functions.TimerTriggerCSharp1 ---> Microsoft.CodeAnalysis.Scripting.CompilationErrorException : Script compilation failed. at async Microsoft.Azure.WebJobs.Script.Description.DotNetFunctionInvoker.CreateFunctionTarget(CancellationToken cancellationToken) at C:\projects\azure-webjobs-sdk-script\src\WebJobs.Script\Description\DotNet\DotNetFunctionInvoker.cs : 340 at async — Billy B
– Billy B, Commented Aug 28, 2018 at 10:22
Is it the complete callstack? Looks like something is missing... — Kzryzstof
– Kzryzstof, Commented Aug 28, 2018 at 10:39
I've attached the error message image, any ideas? I'm baffled. — Billy B
– Billy B, Commented Aug 28, 2018 at 10:45

Mikhail Shilkov · Accepted Answer · 2018-08-28 11:40:45Z

1

You are using binding attributes in C# script, which is wrong... The referenced solution is using precompiled .NET project, not script.

When using script, make sure that your function.json file looks like this:

{
  "bindings": [
    {
      "schedule": "0 */30 * * * *",
      "name": "myTimer",
      "type": "timerTrigger",
      "direction": "in"
    },
    {
      "name": "OutputBlob",
      "type": "blob",
      "path": "mycontainer/myblob.txt",
      "direction": "out"
    }
  ],
  "disabled": false
}

then remove attributes from Function definition:

public static void Run(
    TimerInfo myTimer, 
    out string OutputBlob, 
    TraceWriter log)

and finally assign the blob content directly in the end of the call

// ... 
            OutputBlob = csv.ToString();
        }
    }
}

Overall, my advice is to move to precompiled C# project (as the one in the question you referenced). That takes some setup (in Visual Studio or VS Code) but it will help you avoid the issues and find the errors faster and more exactly.

answered Aug 28, 2018 at 11:40

Mikhail Shilkov

35.3k4 gold badges78 silver badges110 bronze badges

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

4 Comments

Billy B Over a year ago

Wow, fantastic, that has worked an absolute treat. Thank you so much for your time, pointers and tips. Are there any reference books you can recommend for a beginner like myself?

Mikhail Shilkov Over a year ago

@BillyB Great to hear! I don't know any reference books, you should spend time reading the docs, but I do realize they are fragmented and sometimes confusing. People ask lots of questions on stack overflow. Btw feel free to upvote the answer too ;)

Billy B Over a year ago

Thanks Mikhail for the advice, I will do. I've tried to up vote but I only joined yesterday and doesn't look i got enough reputations.. less than 15 don't count it says :-( Soon as I am over the 15 threshold that will definitely get my vote, and once again massive Thank You

Mikhail Shilkov Over a year ago

@BillyB Haha, that's nice of you. Welcome to SO!

Peter · Accepted Answer · 2018-08-27 21:02:14Z

0

I think you can just assign it. Output blob is a string:

           OutputBlob= csv.ToString();

answered Aug 27, 2018 at 21:02

Peter

28k8 gold badges69 silver badges85 bronze badges

1 Comment

Billy B Over a year ago

Thanks Peter.. Tried it and still getting error as per above.. Exception while executing function: Functions.TimerTriggerCSharp1 Microsoft.Azure.WebJobs.Host.FunctionInvocationException : Exception while executing function: Functions.TimerTriggerCSharp1 ---> Microsoft.CodeAnalysis.Scripting.CompilationErrorException : Script compilation failed. at async Microsoft.Azure.WebJobs.Script.Description.DotNetFunctionInvoker.CreateFunctionTarget(CancellationToken cancellationToken) at C:\projects\azure-webjobs-sdk-script\src\WebJobs.Script\Description\DotNet\DotNetFunctionInvoker.cs : 340

Collectives™ on Stack Overflow

Azure Functions - SQL DB Extract to Output Blob

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related