Universal Connections Part 4: SQL Server

Welcome to part four of our multipart pasTransfer Universal Connection series.  Today, I am going to introduce you to the pasTransfer SQL Server connections and give you an overview of how the connections can be used to read and transform data in a SQL Server database.
 
SQL Server Source Connection

The SQL Server source connection allows the extraction of data from a SQL Server database using a custom queries and stored procedures.  After providing valid login credentials, you have the opportunity to enter custom code into the code editor.  You can use any combination of queries, views, stored procedures, and functions that return a result set.
 

 
In the above example, I have provided two queries that will yield the same results – the first being a SELECT statement from the SQLServerTest table and the second calling the stored procedure dbo.GetTransactions.  The connection executes everything in the code editor within a single batch so batch terminators are not supported.  If multiple result sets are returned by the query, we only use the first one.  As you can tell above, we are also able to declare variables.  The query editor supports macros, so we are also able to pass variables in from pasUnity.  These macros are global level variables, meaning we cannot use macros that rely on lines of data.  Since macro support is always enabled in the code editor, make sure you escape any macro block start characters (square brackets [ ) with the appropriate escape character for pasTransfer (backslash \ ).
 
After you create a query, you can change some advanced options for timeouts and transactional support, and then you can begin mapping each column returned in the data set.  On the left side are all the column names that the query returns.  You can click and drag these columns to macro-enabled fields to generate custom COLUMN macro commands.


 
Because this example takes place in the Ledger wizard, we were prompted for fields such as Account Code, Description, Transaction Date, and Amounts.  This varies wizard to wizard.  The Account wizard prompts for fields related to a Chart of Accounts such as Account Code, Active, and Status.  The Vendor wizard prompts for Vendor related fields such as Vendor Name, Vendor Addresses, and Vendor Phone Numbers.  The General wizard has no requirements and can be used for any sort of data, so the only thing you need to supply is an attribute.


 
If you would like to see the data returned by the query, you can preview it at this point.  After you have mapped all the fields, you can proceed through the wizard, import the source data, and proceed on to the destination connection.

SQL Server Destination Connection

The SQL Server destination connection allows you to insert data into a SQL Server table or view.  Inserting into a view is interesting, because a view will contain a single statement that returns a data set, but the statement can contain joins.  If the insert statement would affect multiple tables, execution is prevented.   The connection generates an INSERT statement, so when you provide login credentials, that user must have insert permissions for whatever table is the target of the insert.
 
After importing source data and applying other features such as mapping and aggregation, you can provide credentials for making a connection and other additional options such as timeouts and transactional support.  Once credentials are validated, you can get a list of all the tables and views that you have access to in the target database by pressing the refresh button.  Select the object you want to insert data into, and you can begin mapping data to columns.  You will notice you have two tab pages on the left.  The Detected Macros lists all macro commands that we detected in the source data, while Detected Columns lists all possible columns in the table you can populate.  You cannot violate constraints, so if a column is not nullable, you have to provide a macro for it unless it has a default value constraint.  Macros cannot resolve to a NULL value, only an empty string.  The only way to insert a NULL value is to omit the column from the mapping.  Drag column names to the grid to insert new rows, and drag macros to the grid to define the values for the column.
 

 
After you have supplied all macros for the insert, you can proceed to the end of processing.  If you encountered no errors during your insert, new data will be in your table.
 

 
Conclusion

We have now introduced you to the pasTransfer SQL Server universal source and destination connections.  You have learned how to use pasTransfer to import data using the SQL Server source connection and custom queries and transform that data and insert into a table or view with the SQL Server universal destination connection.  We hope you found this part of series educational and informative.  Come back next week for part five of our series introducing the OLEDB universal connections.