How to import DataSetUtility library - Community

TJ (32 posts)

August 6, 2018 08:36 AM

Accepted Answer

Hi,

I need to query database and send result via email.

I found the link https://www.arcanadev.com/support/kb/K00000563 which requires DataSetUtility scirpt library.

I am trying to format the data, when I go to script editor, I do not see 'DataSetUtility' in the included script libraries. How I could import this library into my console?

Thanks

Bill Staff (604 posts)

August 6, 2018 08:54 AM

Accepted Answer

You can either create the library yourself (the code for the library is included in the article) or you can download the export file that includes the library and sample job (see the "Sample Job" section at the beginning of the article) and import that file.

TJ (32 posts)

August 6, 2018 09:34 AM

Accepted Answer

when I executed the code in the link as below, I got the error 'Unable to cast object of type 'ArcanaDevelopment.adTempus.ApplicationIntegration.Internal.ScriptParameters' to type 'ArcanaDevelopment.adTempus.ApplicationIntegration.IDatabaseTaskScriptParameters'

Imports System

Imports System.Collections.Generic

Imports ArcanaDevelopment.adTempus.Shared

Imports ArcanaDevelopment.adTempus.ApplicationIntegration

Imports System.Data

Public Class UserScript

Inherits ArcanaDevelopment.adTempus.ApplicationIntegration.DatabaseTaskScriptBase

Public Overrides Function Run() As Object

'The Parameters.DataSet object provides access to the data fetched from the database.

If Parameters.DataSet.Tables.Count=0 OrElse Parameters.DataSet.Tables(0).Rows.Count=0 Then

'no data returned. Don't set the variable. Notification step can use empty variable condition to skip sending message.

Return True

End If

'Call the helper function to turn the data into an HTML table

Dim htmlTable=DataSetUtility.DataSetToHtmlTable(Parameters.DataSet)

'put the HTML into a variable to be used in the notification task

adTempus.JobVariables("DataSetHtmlTable")=htmlTable

Return True

End Function

End Class

Bill Staff (604 posts)

August 6, 2018 09:58 AM

Accepted Answer

You will get that error if you use the Test button in the script editor, but if you run the job it should work properly. This is happening because the script editor doesn't have the right context information to pass to the script when you test it. When you run it as part of the database task, the context will be set correctly.

TJ (32 posts)

August 6, 2018 01:20 PM

Accepted Answer

Is Adtempus job able to generate a .txt file when executing database query statement? I could put %DataSetHtmlTable% into that txt file and send notification message with this attachment.

Thanks.

Bill Staff (604 posts)

August 6, 2018 02:25 PM

Accepted Answer

The Database Task only creates XML files (DataSet XML format; see the Database Operation Task documentation). If you want the data in a different format you will need to adapt the sample DataSetToHtmlTable code to produce the format that you want.

If you want to attach it to the e-mail message your script would need to save the formatted data to a file, then put the file name into a variable instead of the file content. In your notification task, you would then use the variable in the attachment list. For example, create the file an put the full file name into the "DataFileToAttach" variable. In the Notification Task settings, go to the Attachments page and add a new file, with the file name set to "%DataFileToAttach%". At execution time it will resolve the variable to the name set by your script, and attach the file.

adTempus creates a temporary folder for each job instance. You can save the file to that folder if you want, and it will be automatically deleted when the job finishes. The folder name is stored in the "ADTJobTemp" variable; to get it from your script use

adTempus.JobVariables("ADTJobTemp")