SqlClient | SqlClient provides database connectivity to SQL Server | SQL Database library

I have a requirement to build a SSIS package that sends HTML formatted emails and then saves the emails as tiff files. I have created a script task that processes the necessary records and then coverts the HTML code to the tiff. I have split the process into separate packages, the email send works fine the converting HTML to tiff is causing the issue.

When running the package manually it will process all files without any issues. my test currently is about 315 files this needs to be able to process at least 1,000 when finished with the ability to send up to 10,000 at one time. The problem is when I set the package to execute using SQL Server Agent it stops at 207 files. The package is deployed to SQL Server 2019 in the SSIS Catalog

What I have tried so far

I started with the script being placed in a SSIS package and deployed to the server and calling the package from a step (works 99.999999% of the time with all packages) tried both 32 and 64 bit runtime. Never any error messages just Unexpected Termination when looking at the execution reports. When clicking in the catalog and executing package it will process all the files. The SQL Server Agent is using a proxy and I also created another proxy account with my admin credentials to test for any issues with the account.

Created another package to call the package and used the Execute Package Task to call the first package, same result 207 files. Changed the execute Process task to an Execute SQL Task and tried the script that is created to manually start a package in the catalog 207 files. Tried executing the script from the command line both through the other SSIS package and the SQL Server Agent directly same results 207 files. If I try any of those methods directly outside SQL Server Agent the process runs no issues.

I converted the script task to a console application and it works processing all the files. When calling the executable file from any method from the SQL Server Agent it once again stops at the 207 files.

I have consulted with the companies DBA and Systems teams and they have not found anything that could be causing this error. There seems to be some type of limit that no matter the method of execution SQL Server Agent will not allow. I have mentioned looking at third-party applications but have been told no.

I have included the code below that I have been able to piece together. I am a SQL developer so C# is outside my knowledge base. Is there a way to optimize the code so it only uses one thread or does a cleanup between each letter. There may be a need for this to create over ten thousand letters at certain times.

Update

I have replaced the code with the new updated code. The email and image creation are all included as this is what the final product must do. When sending the emails there is a primary and secondary email address and depending on what email address is used it will change what the body of the email contains. When looking at the code there is a section of try catch that sends to primary when indicated to and if that fails it send to secondary instead. I am guessing there is a much cleaner way of doing that section but this is my first program as I work in SQL for everything else.

Thank You for all the suggestions and help.

Updated Code

I came across this error when modifying a DB first project (using fluent migrator) and scaffolding the EF context to generate models. I have reproduced it by making a code-first simplification. This means that I can't accept answers that suggest modifying the annotations or fluent configuration, because this will be deleted and recreated on the next migration and scaffold.

The simplified idea is that a device has:

• many attributes
• many histories representing changes to the device over time
  • each history entry has an optional location

IOW you can move a device around to locations (or no location) and keep track of that over time.

The code-first model I came up with to simulate this is as follows:

I have an ASP.Net Webforms website running in IIS on a Windows Server. Also on this server is the SQL server.

Everything has been working fine with the site but now I am seeing issues with using a DataAdapter to fill a table.

So here is some code, please note it's just basic outline of code as actual code contains confidential information.

Ok, I'm totally lost on deadlock issue. I just don't know how to solve this.

I have these three tables (I have removed not important columns):

I am using SSIS 2019 and am able to execute SQL Command with ADO.Net Connection Manager. I want to use OLEDB connection manager in a Script component within a Data Flow Task and I am getting the below error:

System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Can someone please let me know if we can use OLEDB Connection in SSIS script component in a Data flow.

Here is code for connection strings

I'm trying to use the SQLProvider for MS SQL Server with F#, but it appears that it's not possible with the recommended setup.

See my module below:

I have a model like below:

I have a busy ASP.NET 5 Core app (thousands of requests per second) that uses SQL Server. Recently we decided to try to switch some hot code paths to async database access and... the app didn't even start. I get this error:

The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

And I see the number of threads in the thread pool growing to 40... 50... 100...

The code pattern we use is fairly simple:

We are seeing the following errors from Hangfire in our logs from time to time:

Execution BackgroundServerProcess is still in the Failed state for 1.12:52:00.6668371 due to an exception, will be retried no more than in 00:00:15 System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection() at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](DbConnection dedicatedConnection, Func2 func) at Hangfire.Server.BackgroundServerProcess.CreateServer(BackgroundServerContext context) at Hangfire.Server.BackgroundServerProcess.Execute(Guid executionId, BackgroundExecution execution, CancellationToken stoppingToken, CancellationToken stoppedToken, CancellationToken shutdownToken) at Hangfire.Server.BackgroundProcessingServer.RunServer(Guid executionId, Object state) at Hangfire.Processing.BackgroundExecution.Run(Action2 callback, Object state)

It seems like we are maxing out the number of connections in the SQL Connection pool.

I would like to to detect this somehow and clear out the pool. How do I detect it? Can I use Job Filters for this?