Meet Power CLR: SQL Server Integration with APIs, Files, Webservices, FTP and more!

Why was the 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, either due to some technical difficulty for 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 another reason that makes more sense to process this data by 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 the PowerCLR?

The 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 SQL Server integration capabilities like for example:

• Reading and writing plain text files.
• Reading and writing of text files with layout defined by character position.
• Reading and writing CSV files, identifying file separators and headers as columns in a table.
• Reading and writing Excel files.
• Importing binary files into 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 (SSN, EIN, State Registration, E-mail, ZIP code , Telephone number, etc).
• Formatting numbers, dates, strings.
• Advanced encryption functions.
• Integration with ChatGPT.
• Export of a query result to an HTML table (which can be sent by e-mail).
• Removal of HTML and RTF tags from a string.
• Encode and decode URLs.
• Sending email without relying 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 recording in the server's Event Viewer.
• Listing, reading and writing in the list of server services.
• Listing, reading and writing to the listing of server processes.

Once installed and published, the 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, run manually or schedule automatic runs with SQL Server Agent or another task scheduler.

What are the advantages of using the Power CLR?

In addition to ease of integration, we can also gain performance with the Power CLR, which has functions for handling strings 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 lets you use some functions in older versions that only exist in newer versions of SQL Server, such as the STRING_SPLIT function, JSON read functions, and more.

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

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

If your company has a specific need, new features can be added as needed, in a personalized way.

Power CLR demo

Do you want a budget request?

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

Fill in your details below and we'll get in touch to talk better about your need and start a successful partnership.