• Built-in support to start SSIS packages as "jobs" from within TOPICA.

• Jobs / packages may run asynchronously. I.e. once started, a job many run for a long time, and the end user may go on with other tasks.

• Database servers are normally set up with very limited (if any) connection to the outside world. That is, it is often not possible to communicate with external websites over the internet.

  This may severely limit what you can do in SSIS packages deployed in this way. For example, it might not be possible to call webservices hosted by 3rd parties, nor use public FTP sites.

• If the production environment contains a "private" FTP server, that is reachable from both the web server AND the database server, it is possible to implement import jobs by using this private FTP server to exchange files. But it complicates building the SSIS packages - compared to running SSIS packages deployed to the file system of the web server or a remote server.

Starting SSIS packages on the database server programmatically requires extensive rights like sysadmin. To circumvent this, the TOPICA Framework contains a queue (actually a table) in which jobs are queued, Starting SSIS package functionality is placed on SQL Server with only an order form in the TOPICA Framework.

• The database contains a "queue" (actually a database table "QueuedJob")

• TOPICA contains a user interface, that is used to place jobs in the queue.

• The database contains an SQL Agent "ExecuteOrderedSSISPackages", that scans the queue and starts the jobs.

"ExecuteOrderedSSISPackages" scans all databases on the SQL Server for the table "Queuedjob" looking for ordered job not yet executed. The first job found is executed by calling a stored procedure "StartSSISJob" which executes the ordered package with the specified parameters and returns the result to the SQL server Agent job.

Before advancing to the next job the QueudJob is updated with execution end time and result.

If the end user keeps the order form open the result is shown in the bottom of the form meaning "ExecuteOrderedSSISPackages" must be schedule to start very frequently.

Reading names of packages on Sql Server requires sysadmin rights. To bypass that a scheduled Sql Server Agent job "Maintain availablejob" updates a table "AvailableJob" in all databases on the server having that table.

In the actual configuration you can enabled the jobs you want to be able start.

Topica supports starting SSIS packages deployed on a SQL Server.

Use Visual Studio to create the SSIS package and deploy it to the SQL Server.

Make the job available for your configuration by enabling it on the "Job" tab in the datadictionary menu.

Order the job on the "Order job" tab in the import menu choice of the maintenance menu. When the job finishes the result is shown at the bottom of the page.