Why was Power CLR created?

A very common scenario today is the need to create integrations between a SQL Server database and external resources, such as files, FTP and APIs, especially in automatic routines for processing and enriching data through the consumption of information from third-party services or companies.

For complex projects with large volumes of data, ETL/ELT tools such as SSIS, Pentaho and Azure Data Factory are widely used to meet this need and fulfill this function very well.

However, some companies may choose not to use such tools, whether due to some technical difficulty in implementation (SSIS does not have native support for APIs and HTTP requests in general, ZIP files and FTP and SFTP protocols, for example), possible costs involved or some other reason that makes more sense to process this data through the database itself and end up creating insecure, difficult to maintain and non-functional solutions for this, such as OLE Automation procedures or xp_cmdshell.

Aiming to meet this type of need, where the company does not want or cannot use external tools to process this data and to bring more security, robustness and ease of use than traditional solutions, Power Tuning launched the product Power CLR.

 

What is Power CLR?

Power CLR is a set of more than 200 functions and procedures, compiled using the C# language and published in a SQL Server database, which allow you to extend the integration capabilities of SQL Server such as:

  • Reading and writing plain text files.
  • Reading and writing text files with layout defined by character position.
  • Reading and writing CSV files, identifying the file separators and headers as columns in a table.
  • Reading and writing Excel files.
  • Importing binary files to the database and vice versa.
  • Integrations with REST APIs through HTTP requests (GET, POST, PUT, etc.).
  • Reading, listing and writing files in FTP and SFTP protocols.
  • Data validations (CPF, CNPJ, State Registration, Email, CEP, Telephone, etc.).
  • Formatting numbers, dates, strings.
  • Advanced encryption functions.
  • Integration with ChatGPT.
  • Exporting the result of a query to an HTML table (which can be sent by email).
  • Removing HTML and RTF tags from a string.
  • Encode and decode URLs.
  • Sending emails without depending on Database Mail.
  • Integration with Active Directory (AD) for listing groups, members, permissions and more.
  • Integration with Analysis Services for metadata reading, backup, restore and cube processing.
  • Integration with SQL Server Agent to list jobs, execute, stop execution, wait for execution and backup jobs.
  • Reading and writing to the Windows registry (Regedit).
  • Listing, reading and writing in the server's Event Viewer.
  • Listing, reading and writing the server service listing.
  • Listing, reading and writing the server process list.

 

Once installed and published, Power CLR works transparently, as if they were traditional T-SQL functions and procedures, where you can integrate your database with APIs and files for different routines, execute manually or schedule automatic executions with SQL Server Agent or another task scheduler.

 

What are the advantages of using Power CLR?

In addition to ease of integration, we can also gain performance with Power CLR, which has string processing functions and mathematical calculations that can increase the performance of your query by up to 100x, as is the case with the Power CLR Split function when compared to a Split function created using Transact-SQL.

Want to know more advantages? Power CLR allows you to use some functions in older versions that only exist in newer versions of SQL Server, such as the STRING_SPLIT function, JSON reading functions and much more.

Speaking of version and edition support, Power CLR is compatible with SQL Server 2005 onwards and all editions are supported (Enterprise, Developer, Standard, Web and Express).

That's right, even SQL Server Express can utilize the benefits of Power CLR, without any limitations. In fact, Azure SQL Managed Instance is also supported.

If your company has any specific needs, New features can be added as needed, in a personalized way.

 

Power CLR Demo

 

Do you want to request a quote?

Do you need help integrating your SQL Server database with files, APIs or other services?

Fill in your details below and we will contact you to discuss your needs further and begin a successful partnership.

 

← Back

Thank you for your response. ✨