Jack Weakly
Author Archives: Jack Weakly

Why RPA isn’t just trendy

It seems like RPA, or Robotic Process Automation, is all the rage in innovation circles, but ultimately its true benefit is defined by its ability to add value to your company. But how does RPA benefit you? And at what cost?
As leaders in business process automation since 1994, we would love to share insight on Robotic Process Automation.
While seemingly a buzzword, RPA adds value to your organization by automating processes typically performed by employees, while increasing accuracy and minimizing processing time. This is not only possible for one application or process, but can span multiple applications as necessary, including desktop apps, email, Excel, terminal emulators, databases, and more.
There is measurable value to be gained through the addition of Robotic Process Automation systems. To start, it is estimated that 30% of current technology jobs will be replaced with Robotic Process Automation systems by the year 2025 (whatisrpa.com 2017). This means that your organization is currently spending time and money to complete processes and tasks that can and will be replaced in the near future. The bigger picture is that these processes aren’t just replaced by RPA, but they are actually improved and streamlined.
RPA implementation has many benefits beyond just automation. It delivers increased accuracy, leading to up to a 40% reduction in time spent and saves 30% of the typical costs associated with completing processes (whatisrpa.com). These robots also work 24 hours a day with no breaks, freeing up your workforce to direct their efforts elsewhere.
Now, this all sounds great for business, but how difficult is the implementation of Robotic Process Automation?
To start, ILINX automation tools have a small learning curve and a very intuitive user interface. In addition, choosing to implement ILINX, which provides a robust and customizable RPA system, means you have the ImageSource team to aid you in the deployment of your Robotic Process Automation system to ensure that you and your robotics implementation are successful.
So, if you want to be on the cutting edge of innovation while saving your organization time and money, visit ImageSource.com or ILINX.com today.
Jack Weakly, Marketing
ImageSource, Inc

Storing content outside of SQL Server for ILINX Content Store using SQL FILESTREAM

By design, ILINX Content Store stores documents within the SQL database as BLOBs. There are many advantages to this design (security, performance, etc.) but sometimes there is a reason to store the documents outside of the SQL database. SQL Server has a method to do this called FILESTREAM. FILESTREAM integrates SQL Server with the NTFS file system by storing varbinary(max) data outside of the SQL database. FILESTREAM uses the NT system cache for caching file data: this helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

The Advantages of FILESTREAM

One of the main reasons to implement FILESTREAM would be because your documents are generally larger than 1MB in size, storing them outside the database can have a performance advantage. If these are TIFF documents, then this 1MB threshold would be on a per-page basis. This is due to how ILINX Content Store stores TIFF documents. By design, ILINX Content Store splits multipage TIFFs into single pages to allow for users to perform actions on single pages of a document: things like a reorder of pages, single page delete, or rotation.

I recently worked with a customer who scans their documents in as 400DPI color or grayscale TIFF. These documents are generally over 1MB per page and it makes sense to utilize SQL FILESTREAM to store these documents outside of the database and on the file system. With the documents stored in their database, their database would quickly grow to multiple terabytes in size and retrieval could be slower for these 100MB + documents.

One thing to note, is that it is best to make the decision on whether or not FILESTREAM will be utilized before the ILINX Content Store application is in use. Enabling FILESTREAM for an application without documents in it is a very fast process but enabling it for an existing application can take days or weeks. This is because of the file copy that has to occur to migrate the content from in-SQL to outside-SQL.

Step By Step – Activating FILESTREAM

To enable FILESTREAM for an ILINX Content Store application, follow the steps below. Please note that these steps are specific for SQL Server 2008 or 2012. The ILINX Content Store application in the example is named “Sample Application”.

  1. Enable FileStream for the SQL Service via SQL Server Configuration Manager
    a. Open the SQL Server Configuration Manager, Start > All Programs > Microsoft SQL Server (2008 or 2012) > Configuration tools > SQL Server Configuration Manager
    b. Right-click on “SQL Server Service” and select “open”
    c. In the right pane right-click on “SQL Server(instance name)” and select “properties”
    d. Click on the “FILESTREAM” Tab and check the box to “Enable FILESTREAM for Transact-SQL Access”

1

  1. Enable FILESTREAM on the SQL server instance via SQL Server Management Studio
    a. Open SQL Server Management Studio
    b. Right-click on the SQL server instance after logging in and select “properties”
    c. Click on the “Advanced” option on the left
    d. Under FILESTREAM change the “FILESTREAM Access Level” to “Transact-SQL access enabled”

2

  1. Once FILESTREAM is enabled, the SQL Server instance must be restarted.
  2. Add the FILESTREAM Filegroup to the ILINX Content Store database
    a. Right-click on the database you are setting up FILESTREAM on and select “properties”
    b. Select the “Filegroups” option on the left
    c. The bottom group is named FILESTREAM, select “Add”
    d. Type in a name for your FILESTREAM Filegroup (In the below example I simply used the name “FILESTREAM”) you also have the option to set this group to read-only at this time.

3

  1. Create a local directory for FileStream data on disk. I used E:\FILESTREAM. The disk must be local to the SQL server machine, UNC paths are not supported.
  2. Modify database to be able to use the FILESTREAM Filegroup:
    a. ALTER DATABASE ILINX ADD FILE (NAME = FILESTREAM, FILENAME = ‘E:\FILESTREAM\ILINX’) TO FILEGROUP FILESTREAM
  3. Now that FILESTREAM is enabled for both the server and database, we must modify the ILINX Content Store application table to be able to support FILESTREAM. There are requirements that a table using FILESTREAM has a column in it that is unique and has the SQL rowquid designation. ILINX already has a unique column (ICS_FileID), we just need to tell SQL to use it by setting RowGuid value to Yes on ICS_FileID and setting it as the unique column:
    a. alter table [Sample Application] alter column ICS_FileID add rowguidcol
    b. alter table [Sample Application] add unique (ICS_FileID)
  4. The next step involves creating a new column for the binary data that includes the FILESTREAM attribute. If there are already documents in the system, run the query that doesn’t have the ‘not null’ specification:
    a.  alter table [sample application] add BITS1 varbinary(max) FILESTREAM OR alter table [sample application] add BITS1 varbinary(max) FILESTREAM not null default cast(‘0’ as varbinary(max))
  5. This next step copies the data from in-SQL storage column to the new FILESTREAM column created in the last step. This will copy all the documents out of the database and on to the file system. If performing this on an existing application, it is worth noting that you have duplicate data between the following steps and you must be sure that the server has enough storage to support this:
    a. update [sample application] set BITS1 = BITS
  6. Now that all the documents/data are in the FILESTREAM column and out on disk, you can delete the in-SQL storage column. Only do this once the copy is completed:
    a. alter table [sample application] drop column BITS
  7. For the last step, rename FILESTREAM column to the ContentStore required name of ‘BITS’
    a. exec sp_rename ‘dbo.[sample application].BITS1’, ‘BITS’, ‘COLUMN’

Following the above steps will enable FILESTREAM for an application in ILINX Content Store. SQL will manage the file storage and all documents added to the system will now be stored on disk rather than in-SQL.

John Linehan
Senior Systems Engineer
ImageSource, Inc.