SQL automation

SQL AND DATABASE AUTOMATION


VisualCron is an automation, integration and task scheduling tool for Windows




Support for a variety of databases

VisualCron uses 3 different modes for connecting to a database. ODBC, OLEDB and direct mode. Direct mode is supported in MSSQL, Oracle and MySQL. VisualCron is able to connect to all database servers supporting OLEDB or ODBC. VisualCron is optmized for conneceting to relation database management systems.

Connections – globally storing the credentials to your database

The VisualCron Connections are globally stored and can be reused within Tasks and Triggers. This means that you create a Connection once and you can then select it in a Task or Trigger. If you later need to change a password for example you just edit the Connection and the new credentials will be used everywhere where the Connection is referenced.

The Connection guide lets you select database and VisualCron will list common connection strings. Once you find a proper Connection string you can edit all connection string properties.

Screenshot of the SQL Connection guide in VisualCron

Stored procedures, MSSQL Job or SQL text query

VisualCron lets you select between different execution types. You can either add query as text in the text control (which supports T-SQL and PL/SQL language syntax hightlightning) or select an existing stored procedure or MSQL Job.

When selecting a stored procedure the available parameters are refreshed along with their data types. You can edit the value of each parameter – use Variables for dynamic runtime input of values.

Screenshot of the SQL Task configuring a stored procedure in VisualCron

The result and output of the Task

The output of the Task is based on what columns are selected. It is possible to include the field names as well. Optionally, you can even set field separator, text qualifier and line break character.

DTS, SSIS and SSISDB

SSIS – SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a wide range of data migration tasks. SSIS is superseding DTS – Data Transform Services from Microsoft SQL Server 2005. Developers tasked with creating or maintaining SSIS packages use a visual development tool based on Microsoft Visual Studio called the SQL Server Business Intelligence Development Studio (BIDS). This allows users to edit SSIS packages using a drag-and-drop user interface. A scripting environment in which to write program code is also available in the tool. A package holds a variety of elements that define a workflow. Upon package execution, the tool provides color-coded real-time monitoring. DTEXEC executes a package from the command line wherever it may be stored. Before running the package, the tool may be instructed to apply configuration information, which will allow the same package to be reused with slightly different parameters, including different connection strings for its endpoints.

In VisualCron you can execute these packages and use return output and result from them.

SSISDB are packages located in the SSIS catalog. There is a specific Task called the SSIS DB Task for execution in VisualCron.

SSRS – SQL Server Reporting Services

SSRS is a server-based reporting platform that allows us to create and manage a wide variety of different types of reports, and deliver them in a range of formats. We can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. Our reports can draw their data from SQL Server databases, but also from other relational database such as Oracle, and other types of multi-dimensional or XML-based data sources such as SQL Server Analysis Services, Teradata, or Parallel Data Warehouse.

We can present our finished reports directly from the Reporting Services website, called Report Manager, or users can view them directly within their web- or Windows-based applications. End users can run reports on demand or schedule them as subscriptions. We can also install SSRS so that the reports run from SharePoint.

In VisualCron you can schedule execution of existing reports in SSRS through the SSRS Task. In the SSRS Task you decide the output format to be used, for example PDF or Word and where it should be saved locally.

The SQL database Trigger

One of the Triggers in VisualCron is able to monitor a database for changes in a specific query that you create. The query is combined with a Condition and if a value (or Variable) is higher, lower, equal etc than the value from the database VisualCron will fire the Trigger.

Scroll to Top