pasTransfer Ledger XML Destination




pasTransfer Ledger XML Destination


The pasTransfer Ledger XML Destination destination connection is used to generate files consumed by the pasTransfer Ledger XML Source connection in subsequent transformations.


This allows for complex integrations in which the data must undergo multiple transformations or for the combining of multiple output files. 


Common Scenarios:


Source data is derived from multiple files or query results that must be extracted individually and only after all source files have been successfully validated and/or mapped combined into a single output for submission to the destination as an atomic batch that succeeds or fails as a single unit (all or nothing).


Source data must undergo multiple mapping phases using multiple mapping sheets for purposes of summarizing (grouping with aggregate functions) or complex mapping in which a first pass transforms key attributes for use by the second phase mapping sheets.


The file format is proprietary to the application and is not intended for any usage scenario outside of the application.


The file format is backwards and forwards compatible for any build of the application with the same major version number.


pasTransfer XML Export Page


This page is used to specify what content is persisted to the exported native XML document generated by the connection. 


Detected Macros: Along the left hand side of the page is a list of all the macro commands that can be used to extract the values of the standard source fields (accessed with the [Copy] macro), the dynamic source columns (accessed with the [Attribute] macro), and the data appended during the mapping process (accessed with the [Mapping] macro).  You may drag and drop items from the detected macros listing to new or existing rows on the Custom Attributes tab to write an export expression.

Export Options: This tab determines the behavior during the export process

Export Data to SQL Server: If this option is selected an additional SQL Server Destination Query Page will be added to the wizard that allows the native XML to be accessed as an XML variable named @PASXML by a Transact-SQL query sent to SQL Server.

Preserve Zero Amounts: If this option (available only in the Ledger wizard) is selected those rows that have zero-values amounts will not be exported.

Custom Attributes: By default all attributes on all rows of data imported from the source connection and any additional attributes added during the mapping process are exported but that behavior can be overridden or augmented using this table.  To suppress attributes from the source add an entry to this table with the same name as the incoming attribute and a value of [Blank] which will remove the value.  To create a new attribute add a row with the name of the attribute you want to create and provide a macro expression in the value column.  You can also override existing attributes by adding a row using the same name as an existing attribute and providing a macro expression in the value column.


SQL Server Destination Query Page


The SQL Server Destination Query Page allows a user to enter credentials, select a database, and choose additional options in regards to execution of an export query.


Connection Tab

SQL Server Name:  The location/address of the SQL Server database.

Authentication:  The method for signing into the SQL Server database.  Windows will attempt to log in with the users Windows Credentials.  SQL Server will allow you to manually enter a user name and password for the SQL Server database.  Optionally, you can choose to save the password afterwards.

Database Name:  The name of the target database which contains the table or view which is to be inserted into.

Enable Macros: If this option is selected macro commands can be used in any field with a yellow background color.

Query Tab

Query: Use the Transact-SQL language to create statements to process an XML variable named @PASXML which will contain all of the native XML exported by the destination connection.  The connection will break your script into batches separated by the use of the word GO on a line by itself and will execute each batch one after another.  Each batch will essentially have a declaration added to it of DECLARE @PASXML xml; and that variable will contain the entire native XML document.

Advanced Tab

Connection Timeout:  The amount of time to spend attempting to make a connection to the database.  The default time is 15 seconds.

Command Timeout:  The amount of time to spend attempting to complete the commands or transaction before disconnecting.  It is advised to use a higher command timeout if you are inserting a lot of data or working with a slow connection.

Use Transactions:  A transaction is a single unit of work.  Enabling transactions allows for an all-or-nothing insert.  If any lines encounter a problem, the transaction will roll back and all modifications will be erased.  If a transaction is successful, all modifications were successful and become a permanent part of the database.


Copyright © 2023 pasUNITY, Inc.


Send comments on this topic.