Create a lifecycle script and prepare a Script transformation example

Open ETL

In the intended Data Warehouse (DWH) project, click ETL in the toolbar. This tab provides the package, script, and transformation commands.

Open Scripts

Click Scripts to open the list of lifecycle-script definitions.

Create a new script

Before creating the new definition, inspect the Script type, Sequence number, and package assignments of relevant existing scripts. Note where the new script should fit in that order. Return to Scripts and click New to open the script editor.

Choose the execution stage

In the script editor, select the required Script type. The editor also contains the name, description, sequence, Inactive checkbox, and statement area; workflow script types use the package grid on the right. Choose the type according to when the statement must run:

1. Pre-creation — Runs before Data Warehouse (DWH) object creation or synchronization. Use it for setup needed by those objects, such as SQL function definitions used by transformations.
2. Post-creation — Runs after Data Warehouse (DWH) object creation or synchronization. Use it for setup that requires the created database objects, such as stored SQL routines.
3. Pre-workflow — Runs before workflow package execution. Use it for a check or action that must precede that workflow, and assign the script to the intended package.
4. Post-workflow — Runs after workflow package execution. Use it for an action needed after that workflow, and assign the script to the intended package.
5. Pre-deployment — Adds the script to the DACPAC deployment file for actions before deployment. Choose it for deployment-specific preparation.
6. Post-deployment — Adds the script to the DACPAC deployment file for actions after deployment. Choose it for deployment-specific follow-up work.
7. Repository extension — Extends or customizes the AnalyticsCreator repository through SQL. Use it when the task explicitly requires changes to repository functionality.

Table-check example: select Pre-workflow when the table must be available before the workflow starts. A check that runs before loading cannot confirm the outcome of that load.

Assign the workflow packages

For Pre-workflow or Post-workflow, find each intended package in the right-hand grid and select its Run checkbox. Clear unintended assignments. For another script type, continue to Enter the name and description; workflow package assignments apply to the two workflow types.

1. Package — A displayed column identifying a workflow package to which a Pre-workflow or Post-workflow script can be assigned.
2. Run — A checkbox in each package row. Select it to assign this script to that package; clear it to remove that assignment. This changes the script assignment, not whether the whole package is enabled.

The capture uses a package named Workflow package. Select the package required for your task. Selecting Run assigns the script; it does not start the package.

Enter the name and description

Enter a distinct Name and a Description explaining the script’s stage and effect. For a table check, a naming example is Check required source table; describe which table it checks and why.

1. Name / Description — The script's identifying name and purpose note. Describe the intended execution stage and effect so that you can recognize the definition later.

Set the execution order and enabled state

Using the order reviewed in Create a new script, enter a Sequence number that places this script in the intended order. Then select or clear Inactive according to whether this definition should be enabled.

1. Sequence number — The script launch order, expressed as 1, 2, 3, and so on. Review the other scripts for the same stage and package before choosing a number; do not rely on equal numbers to establish a particular order.
2. Inactive — Disables this script when selected. Select it if the definition must be saved without enabling execution; clear it when the script is intended to run in its configured context.

For example, use 1 when the script should run first in its relevant sequence. If the definition is being saved for later review, select Inactive. The capture shows 1 and Inactive clear; these are example settings.

Enter the SQL statement

Select Original above the statement area and enter the complete statement required by your task. For the Pre-workflow table-check example, replace both occurrences of dbo.SourceTable below with the required table identifier:

IF OBJECT_ID('dbo.SourceTable', 'U') IS NULL
    THROW 50001,
        'Required source table dbo.SourceTable was not found.', 1;

1. T-SQL / SQL — Language labels for the SQL Server statements used here. Use syntax supported by the target database and the script context.
2. OBJECT_ID('dbo.SourceTable', 'U') — Looks up the identifier of the user table dbo.SourceTable in the execution database. The literal U restricts the lookup to a user table.
3. IS NULL — Tests whether the lookup returned no identifier. This can indicate a missing table, an incorrect database or object name, or insufficient metadata visibility.
4. THROW 50001, ..., 1 — Raises a SQL error with the stated message when the lookup returns no identifier. 50001 is the example error number and 1 is its state; neither is an AnalyticsCreator setting.
5. dbo.SourceTable — The example table identifier. Replace it in both the lookup and the error message with the schema-qualified table required by your task. The statement does not create that table.

The statement reports a problem when the table lookup returns no identifier. It does not create the table or start the workflow. If you add it to a larger SQL batch, terminate preceding statements with semicolons.

1. Original / Parsed — Select Original to enter the statement. Parsed is the other displayed mode; entering a statement here uses Original.

Review and save the lifecycle script

4.9.1. Confirm Script type, Name, Description, Sequence number, Inactive, the full statement, and all referenced identifiers. For workflow types, confirm the intended Run selections.

4.9.2. Click Save. If an error is reported, correct the reported issue and save again.

4.9.3. Remember that an enabled workflow script is assigned for use when the selected package executes. Keep Inactive selected if the script must remain disabled.

Reopen and check the saved script

Click ETL → Scripts, find the script by its Name, and double-click it. Compare the saved Script type, package Run selections, Name, Description, Sequence number, Inactive state, and complete statement with the intended configuration.

Expected Results: the saved definition contains the intended values. If saved inactive, Inactive remains selected. This completes the lifecycle-script definition path.

Define and create the output table

4.12.1. In the navigation tree, expand Layers, then the intended layer and schema. Right-click Tables and select add externally filled table. An externally filled table stores results written by the script.

4.12.2. In the table editor, set Table Schema to the intended output schema and enter Table Name. For this example, use dbo and CategoryNameCopy only if those are the names chosen in Confirm the category-copy input and output.

4.12.3. On Columns, define the columns shown below. Select Has primary key for the table. CategoryID comes from the input; this example does not need an Identity column.

4.12.4. Review the primary-key design. PK clustered chooses a clustered key when selected and a nonclustered key when clear; use your project’s design. Enter a Primary key name consistent with that design. The SQL example requires a unique key on CategoryID, not a specific constraint name or clustering choice.

4.12.5. Click Create in DWH to create the physical output table in the target database. Resolve any reported definition error, then click Save to retain its definition. Confirm that the physical table exists before executing the copy statement.

Create the Script transformation definition

Right-click an empty area of the diagram and select Add → Transformation. In the Transformation Wizard, select Script in Type, select the intended Schema, and enter an unused Name, such as CopyCategoryNames.

Use the T-SQL script subtype for the SQL example. The command-based Executable subtype uses a different statement format; it is described in Decisions and variations. Complete the wizard with finish, then open the resulting transformation by double-clicking it in the diagram.

Enter the category-copy statement

In the Script transformation editor, enter the following SQL in its script statement area. Replace the example table and column identifiers consistently if you chose different objects in Confirm the category-copy input and output.

INSERT INTO [dbo].[CategoryNameCopy] ([CategoryID], [CategoryName])
SELECT source.[CategoryID], LTRIM(RTRIM(source.[CategoryName]))
FROM [IMP].[Categories] AS source
WHERE NOT EXISTS (
    SELECT 1
    FROM [dbo].[CategoryNameCopy] AS target
    WHERE target.[CategoryID] = source.[CategoryID]
);

1. LTRIM(RTRIM(...)) — Removes leading and trailing space characters (char(32)). A NULL name remains NULL; this expression does not remove every kind of whitespace.
2. NOT EXISTS — Inserts a source row only when its CategoryID is absent from the output. Source identifiers must be unique so that one run cannot attempt to insert the same new key twice.
3. Insert-only behavior — Existing output identifiers are skipped, even if the source name changes. Use a different loading strategy if the task requires updates or deletions.

The statement reads existing output keys before inserting new ones. Use this example when retaining existing output rows is intended; it is not a refresh of all category names.

Review the input and output dependencies

Inspect the transformation’s input and output table lists. AnalyticsCreator attempts to detect SQL dependencies; compare the detected tables with every table read or modified by the statement. Add missing dependencies using these lists.

1. Input tables — Tables read by the statement. The example reads IMP.Categories and also reads dbo.CategoryNameCopy to check existing identifiers.
2. Output tables — Tables modified by the statement. The example inserts rows into dbo.CategoryNameCopy.
3. Script package — The package associated with a Script transformation. A package is created when the transformation is defined; inspect its contents and execution configuration before using it in a workflow.

Confirm IMP.Categories as an input and dbo.CategoryNameCopy as an output. Because the statement also reads the output in NOT EXISTS, review that read dependency when checking the detected table lists.

Save the transformation and inspect its package

Click Save in the transformation editor. Reopen the transformation by double-clicking it in the diagram and confirm its name, schema, T-SQL statement, and input/output dependencies.

Click ETL → Packages to list packages. Find the Script package associated with this transformation and open its definition. Check that it is the intended transformation’s package and review its execution configuration for your workflow. The lifecycle script’s Run grid in Assign the workflow packages does not configure a Script transformation.

Expected Results: the transformation definition, output table, and associated package can be found and inspected. Verify actual data changes when the package is executed through your project’s development execution workflow, using the checks in Expected Results.