Welcome to a new series of blog posts that introduce you to the pasTransfer Universal Connections
The Universal Connections
are powerful source and destination connections in pasTransfer that directly integrate to and from a variety of common file and database formats without any custom coding. pasTransfer has the native ability to parse, generate, and validate hundreds of different file formats and communicate, extract, post, or send data to hundreds of different database systems and services using our pre-built connections. These connections are designed to eliminate the technical challenges and minimize the need to understand to technically understand the source and destination system and focus on the data. For example, a user need only supply a server name, credentials, and business unit name and that would be enough for pasTransfer to pull a detailed trial balance or post a balanced general ledger transaction. Basically, the pasTransfer pre-built connections are all there to solve specific scenario challenges. The Universal Connections
solve a completely different need: ad-hoc connections to and from common databases, services, and file formats that allow power users and professional implementers alike to build powerful connections in scenarios that do not quite demand a pre-built connection or require highly focused custom logic that would not merit a pre-built connection.
Each pasTransfer wizard now contains the following seven Universal Source
and seven Universal Destination
- XML: These connections allow for parsing of XML markup files (with and without schemas) and the generation of new XML files along with the application of XSLT stylesheets which additionally can be used to reflect the data into HTML or other file formats.
- Excel: These connections allow for parsing of data from Microsoft Excel spreadsheets that can be read with the Microsoft ACE provider and Microsoft Open XML API and the generation of new Open XML files that can be opened in Excel or other tools.
- Text: These connections enable parsing and generation of various basic text file formats including fixed width and delimited formats, any code page, custom column and row delimiters, and even limited support for headers, footers, and groupings.
- SQL Server: Direct reading and writing to Microsoft SQL Server databases using straight up Transact-SQL code or even calling advanced stored procedures allows for the building of connections that can both validate and integrate.
- OLEDB: Similar to the SQL Server connection but optimized to directly interface against a wide range of OLEDB providers for highly performant connections to most modern database systems.
- ODBC: These universal connections allow you to extract and load data to and from any database system for which you cannot obtain OLEDB drivers but have an ODBC driver.
- pasTransfer Native XML: Each pasTransfer wizard essentially takes data from a source and converts it to a neutral format from which it then converted again to a destination format. This Source=> Neutral => Destination conversion allows us to only have to write conversion and validation to or from a particual product or file format one time and through the use of the neutral format not have to account for every possible combination of source and destination manually. The pasTransfer Native XML format is the representation of that neutral format and allows for several advanced integration scenarios including the merging of output from various sources.
This series will introduce you to each of these Universal Connection types and will explore the scenarios for use and even get into the technical weeds about some of the more powerful or obscure uses of these connections.