Help Manual - Premium SQL Server Source

Documentation for the SSIS Premium SQL Server Source component

Using the Premium SQL Server Source Component

The Premium SQL Server Source Component utilizes the SQL Server Connection Manager to facilitate reading from databases. There are three pages for configuration:

  • General
  • Columns
  • Pre & Post Commands

General Page

The General page of the Premium SQL Server Source Component allows you to specify the component's general settings.

SQL Server Source.png

Connection Manager

The Premium SQL Server Source Component requires the SQL Server connection manager.

    Data Source

    The Data Source drop-down displays a list of available tables and views from the database specified in the Connection Manager. Selecting a table here will automatically populate the Command property. This is a quick and easy way to generate a basic select statement for reading from the database.

    Command Timeout

    The Command Timeout option allows you to specify the number of seconds for the command timeout values. The default value is 120 seconds.

    Transaction Type

    This option allows you to specify the type of transactions by choosing Explicit and selecting an Isolation Level or by choosing Implicit.

    Transaction Isolation Level (Available only when "Explicit" Transaction Type is selected)

    The Transaction Isolation Level option allows you to specify concurrency behaviors for database tables. Different data providers support different isolation levels.

    Parameterize Command

    Parameterizing commands protects against SQL injection. Only disable this option when it is required, and you have absolute control over the values being merged into the command.


    Command

    The Command textbox is the command text that will be executed over the Connection Manager to read data from the database. A basic select statement can easily be generated by selecting a table or view from the Data Source property. You can then further customize the command to your liking to perform powerful queries.

    The Command textbox now supports the use of User and System Variables. Simply select a variable under the Insert Variables drop-down menu, and a placeholder value will be inserted into the filter text.

    Import

    Loads SQL from a file into the Command property.

    Export

    Save the SQL in the Command property to a file.

    Preview

    Opens a preview dialog that shows the result (up to the first 200 rows) of executing the text in the Command property over the specified Connection Manager.

    Note: If the command makes any changes to the database, the changes will appear in the preview, but are rolled back immediately. Changes to the database will only be commited at runtime.

    Refresh Component Button

    Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.

    Expression fx Button

    Click the fx button to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

    Generate Documentation Button

    Click the Generate Documentation button to generate a Word document that describes the component's metadata, including relevant mapping and more.

    Columns Page

    The Columns page of the SQL Server Source Component shows you the available columns based on the settings on the General page.

    SQL Server Source - Columns.png

    On the top left of the grid, the checkbox can be used to toggle the selection of all available SQL Server fields. This is a productive way to check or uncheck all available fields.

    Note: As a general best practice, you should only select the SQL Server fields that are needed for the downstream pipeline components. You can do this on the columns page using the checkboxes or on the General page by removing entirely the column from the command.

    Pre & Post Commands Page

    The Pre & Post Commands Page page allows you to specify commands to execute before and after component execution.

    SQL Server Source - Pre and Post Commands.png

    Pre and Post Command textboxes now support the use of User and System Variables. Simply select a variable under the Insert Variables drop-down menu, and a placeholder value will be inserted into the command text. Post Command has been split into Success Post Command and Error Post Command.

    Pre Command

    The Pre Command will be executed in the pre-execute phase. Leave blank to NOT execute any command.

    Success Post Command

    The Success Post Command will be executed in the post-execute phase when it succeeds. Leave it blank if you do not want to execute any command.

    Error Post Command

    The Error Post Command will be executed in the post-execute phase when an error takes place. Leave blank to NOT execute any command.