Exporting data to Azure Data Lake Store with Custom code in Script task Component

• sagar.malandkar

  Grasshopper

  Points: 22

  More actions

  October 9, 2018 at 9:53 am

  #415911

  I need a help. I am trying to export the data to ADLS by converting the result set into the stream.

  When I am trying this with a Console app, No error is thrown, But When I integrate this with the Script task enter code here Component It throws an error at runtime. Also After adding Azure classes Breakpoint is not hitting while debugging.

  private static string tblName = "tblGrid";
  DataTable dataTable = new DataTable();

  public byte[] datared()
  {
    string connectionString = "Data Source=local;Initial Catalog=abc;Integrated Security=True";

    string query = "select * from " + tblName;

    SqlConnection conn = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(query, conn);
    conn.Open();

    // create data adapter
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    // this will query your database and return the result to your datatable
    da.Fill(dataTable);
    conn.Close();
    da.Dispose();

    var a = Encoding.GetEncoding("iso-8859-1").GetBytes(ToByte(dataTable));
    return a;
  }

  public static string ToByte(DataTable table)
  {
    var result = new StringBuilder();
    for (int i = 0; i < table.Columns.Count; i++)
    {
     result.Append(table.Columns.ColumnName);
     result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
    }

    foreach (DataRow row in table.Rows)
    {
     for (int i = 0; i < table.Columns.Count; i++)
     {
      result.Append(row.ToString());
      result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
     }
    }

    return result.ToString();
  }

  public static void Main()
  {
    Program p = new Program();
    var bytes = p.datared();
    MemoryStream stream = new MemoryStream(bytes);

    // Name of the Azure Data Lake Store
    var adlsAccountName = "testadls08";

    SynchronizationContext.SetSynchronizationContext(new SynchronizationContext());

    // Portal > Azure AD > Properties > Directory ID (aka Tenant ID)

    var domain = "177cfeea-xxxx-xxx-853d-xxxx";

    // Portal > Azure AD > App Registrations > App > Application ID (aka Client ID)

    var clientId = "0e499fed-7f23-4b19-98ab-xxxxx";

    // Portal > Azure AD > App Registrations > App > Settings > Keys (aka Client Secret)

    var clientSecret = "6xxxxxxxxxxxxxxxxxxxxxxxx=";

    var clientCredential = new ClientCredential(clientId, clientSecret);
    var creds = ApplicationTokenProvider.LoginSilentAsync(domain, clientCredential).Result;

    var filepath = "/shared/" + tblName + ".txt";
    var adlsFileSystemClient = new DataLakeStoreFileSystemManagementClient(creds);
    adlsFileSystemClient.FileSystem.Create(adlsAccountName, filepath, overwrite: true);
    adlsFileSystemClient.FileSystem.Append(adlsAccountName, filepath, stream);
  }
  }

  An exception has been thrown by the target of an invocation.

  at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.

  UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.

  InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()