Hey everyone, I’m currently developing a custom activity for Sql Server Bulk Insert using Microsoft.Data.SqlClient version 4.1.0.
Encountering a confusing issue, let me elaborate. I’m including the project DLL via NuGet package and adding the Microsoft.Data.SqlClient version 4.1.0 in the Edit Dependencies of NuGet.
Here are some key points to note:
- My activity requires a connection string as input for establishing a connection.
- I’m utilizing the Class Library (.NetFramework) project for activity development.
- Transitioning the activity from legacy to Windows environment.
- The legacy activity operates smoothly without any issues.
Now, let’s discuss the scenarios within UiPath Studio:
- When UiPath.Database.Activities package is not installed:
In this scenario, I simply add my package and utilize the activity. However, upon execution, I encounter the following error:
“Unable to load DLL ‘Microsoft.Data.SqlClient.SNI.dll’ or one of its dependencies: The specified module could not be found. (0x8007007E)”
-
When UiPath.Database.Activities package is installed (maintaining the same Microsoft.Data.SqlClient version 4.1.0):
Even after installing the package and running my activity, the same error persists. -
When Connect to Database Activity is used:
In this scenario, I employ the Connect to Database Activity without configuring it, simply providing the same connection string in the connection string field and choosing to provide to System.Data.SqlClient. Surprisingly, following this, my activity works without encountering any issues.
The issue at hand has led me to explore various solutions:
-
Verifying the files of Microsoft.Data.SqlClient.SNI.x64.dll and Microsoft.Data.SqlClient.SNI.x84.dll in the output directory of the DLL file after building the project from Visual Studio.
-
Attempting to add all packages shown in the UiPath.Database.Activities package in the Edit Dependencies of NuGet package explorer, alongside their respective versions.
-
Adding Microsoft.Data.SqlClient.SNI with Microsoft.Data.SqlClient in the edit dependencies.
-
Manually adding Microsoft.Data.SqlClientx64.SNI.dll in my UiPath project root directory for testing purposes.
-
Comparing the packages inside Microsoft.Data.SqlClient version 4.1.0 for UiPath and mine.
and mine
Despite exploring these solutions, I remain puzzled as to why the same packages work seamlessly for UiPath activities but encounter issues with mine. Additionally, the dependency of my activity on the Connect to Database activity for execution adds to the confusion.
My Code
public class DataTableToSQLTable : CodeActivity
{
[Category("Input")]
[RequiredArgument]
[Description("Datatable to push into SQL table.")]
public InArgument<DataTable> SourceDatatble { get; set; }
[Category("Input")]
[RequiredArgument]
[Description("Datatable coloumns name that map wih sql table columns.")]
public InArgument<string[]> SourceColumns { get; set; }
[Category("Input")]
[RequiredArgument]
[Description("The connection string used to establish a database connection.")]
public InArgument<string> connectionString { get; set; }
[Category("Input")]
[RequiredArgument]
[Description("SQL table name.")]
public InArgument<string> SQLTableName { get; set; }
[Category("Input")]
[RequiredArgument]
[Description("SQL table columns name that map wih datatble columns.")]
public InArgument<string[]> DestinationColumns { get; set; }
protected override void Execute(CodeActivityContext context)
{
DataTable table = SourceDatatble.Get(context);
string[] sourceColumns = SourceColumns.Get(context);
string[] destinationColumns = DestinationColumns.Get(context);
string connectionString = this.connectionString.Get(context);
string sqlTableName = SQLTableName.Get(context);
// Check if the source and destination columns match in length.
if (sourceColumns.Length != destinationColumns.Length)
{
throw new Exception("The number of source columns does not match the number of destination columns.");
}
try
{
// First, try to open a connection to the database to ensure that our connection string is valid.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); // Attempt to open the connection.
if (connection.State == ConnectionState.Open)
{
Console.WriteLine("Successfully established a connection to the database.");
}
else
{
throw new Exception("Failed to establish a connection to the database.");
}
}
// If connection is successful, proceed with the bulk insert operation.
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
{
for (int i = 0; i < sourceColumns.Length; i++)
{
sqlBulkCopy.ColumnMappings.Add("[" + sourceColumns[i] + "]", "[" + destinationColumns[i] + "]");
}
sqlBulkCopy.BulkCopyTimeout = 600; // You can adjust this value based on your requirements.
sqlBulkCopy.DestinationTableName = "[" + sqlTableName + "]";
sqlBulkCopy.WriteToServer(table);
}
}
catch (Exception ex)
{
// It's often useful to provide more specific exception handling.
// For example, you might want to handle SqlException differently to give more detailed DB error messages.
throw new Exception($"An error occurred: {ex.Message}", ex);
}
}
}
UiPath Connect to Database actviity code
using System;
using System.Activities;
using System.ComponentModel;
using System.Diagnostics;
using System.Net;
using System.Security;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Markup;
using UiPath.Database;
using UiPath.Database.Activities;
using UiPath.Database.Activities.Properties;
[LocalizedDescription("Activity_DatabaseConnect_Description")]
public class DatabaseConnect : AsyncTaskCodeActivity
{
private readonly IDBConnectionFactory _connectionFactory;
[DefaultValue(null)]
[LocalizedCategory("ConnectionConfiguration")]
[RequiredArgument]
[LocalizedDisplayName("Activity_DatabaseConnect_Property_ProviderName_Name")]
[LocalizedDescription("Activity_DatabaseConnect_Property_ProviderName_Description")]
public InArgument<string> ProviderName { get; set; }
[DependsOn("ProviderName")]
[DefaultValue(null)]
[LocalizedCategory("ConnectionConfiguration")]
[LocalizedDisplayName("Activity_DatabaseConnect_Property_ConnectionString_Name")]
[LocalizedDescription("Activity_DatabaseConnect_Property_ConnectionString_Description")]
public InArgument<string> ConnectionString { get; set; }
[DefaultValue(null)]
[DependsOn("ProviderName")]
[LocalizedCategory("ConnectionConfiguration")]
[LocalizedDisplayName("Activity_DatabaseConnect_Property_ConnectionSecureString_Name")]
[LocalizedDescription("Activity_DatabaseConnect_Property_ConnectionSecureString_Description")]
public InArgument<SecureString> ConnectionSecureString { get; set; }
[LocalizedCategory("Output")]
[DependsOn("ProviderName")]
[LocalizedDisplayName("Activity_DatabaseConnect_Property_DatabaseConnection_Name")]
[LocalizedDescription("Activity_DatabaseConnect_Property_DatabaseConnection_Description")]
public OutArgument<DatabaseConnection> DatabaseConnection { get; set; }
public DatabaseConnect()
{
_connectionFactory = new DBConnectionFactory();
}
internal DatabaseConnect(IDBConnectionFactory factory)
{
_connectionFactory = factory;
}
protected override async Task<Action<AsyncCodeActivityContext>> ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
{
string connString = ConnectionString.Get(context);
SecureString connSecureString = ConnectionSecureString.Get(context);
if (connString == null && connSecureString == null)
{
throw new ArgumentNullException(UiPath_Database_Activities.ValidationError_ConnectionStringMustNotBeNull);
}
if (connString != null && connSecureString != null)
{
throw new ArgumentException(UiPath_Database_Activities.ValidationError_ConnectionStringMustBeSet);
}
string provName = ProviderName.Get(context);
DatabaseConnection dbConnection = null;
try
{
dbConnection = await Task.Run(() => _connectionFactory.Create(connString ?? new NetworkCredential("", connSecureString).Password, provName));
}
catch (Exception arg)
{
Trace.TraceError($"{arg}");
throw;
}
return delegate(AsyncCodeActivityContext asyncCodeActivityContext)
{
DatabaseConnection.Set(asyncCodeActivityContext, dbConnection);
};
}
}
ashokkarale
Yoichi
Anil_G
Aswini
sonaliaggarwal47
maulik_khunt
sharazkm32
manasrlenka25
Rohi2023
VanjaV