TARGIT Period Table Task is a custom component for SQL Server Integration Services. The component assists in creating and maintaining complex period tables.
The Period Table task helps create and maintain period tables with a wide range of datetime based columns. When the task runs, the period table defined is dropped and re-created.
The TARGIT Period Table component is included in the "TARGIT Custom SSIS Tasks" installation package found in the TARGIT App & Gauge Store or the Download Center on the TARGIT Portal. After installation, a restart of SQL Server Data Tools and the SSIS project is required for the task(s) to appear in the SSIS toolbox.
In the Input tab, the unique dates to be used for creating the period table are selected. If the table is to contain only unique dates from one or more database tables, add the tables and date columns to be included. Dates will be selected as unique and the timestamp part will be included, so be careful with large tables with unique dates as the period table can be unnecessarily large. Consider truncating the time part from dates in the input tables if time is not relevant.
The Span Dates function will inject any missing dates into the resulting period table in a selected interval if it is necessary to have all dates represented, e,g, for use with Balance at Date calculations. It can also be used for creating a period table without table inputs. This will results in a period table with all dates (without the time part) in the selected date range.
Span Dates - From
Span Dates - To
In the Output tab, the properties and columns of the period table to be created are defined. Add a table name and the name of the primary date (datetime) column of the completed period table. The language selection defines available naming for e.g. month names.
In Columns, any number of columns can be defined, all based on the list of input dates. A few simple columns are available when the task is first added, but there is no limit to the number of columns. A right click menu is available for managing the columns. Each column has a name, an output type, either Number of Text, and the formatting function describing what to calculate.
When a new column is added, a new window with the properties of the column pops up. To help define the column output format, use the format helper function. In the example, a numeric column called DateNumber is defined, which will return a full date of the format 20181123. In the Output column format box below, the list of available output column formats is shown.
The period table resulting from the example definitions with DateNumber added:
The following identifiers will be substituted during execution of the task. Examples are presented using the date 24th of august, 1970. #YYYY 4 digit year (1970) Any identifier can be appended with options that can be used to modify the resulting value. There are two options; date shift (days and/or months) and add/subtract. The date shift option has the syntax {nD} or {nM}, where n can be any positive or negative integer and denotes the number of days/months that a date is adjusted before the result is calculated. D is days and M is months. If the days and months options are combined, the days option is calculated first. Examples: The add/subtract option is applied after the date calculation and is only valid for numeric identifiers, i.e. it will work for #M and #MM, but not #MMM and #MMMM. Examples: The options can be combined in any order. Examples: |
|
Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.
|