Use DataAI ETL from SSIS

Execution model: SSIS orchestrates a DataAI Spark job. DataAI is not an SSIS Data Flow component, MSI, or GAC assembly. The DataAI job reads and writes customer Spark/catalog tables, and later SSIS tasks consume the approved results.

1. Prepare the package variables

SSIS variable	Example purpose
`User::SparkSubmit`	Full path to `spark-submit.cmd` or an approved command
`User::DataAiCliJar`	Installed shaded `dataai-spark-cli-<version>.jar`
`User::DataAiConfig`	Customer-owned JSON configuration path
`User::WorkingDirectory`	Restricted working directory visible to the execution identity

The JSON selects the source table, optional output tables, normalization, record keys, rules, and optional minimum quality score. Keep credentials in SSIS, Spark, or the customer's secret facility—not in the JSON.

2. Configure an Execute Process Task

Add an Execute Process Task to the Control Flow.
Set Executable to User::SparkSubmit.
Build the argument string from controlled variables:

--class com.dataai.etl.spark.cli.DataAiJob
"C:\approved\dataai\dataai-spark-cli-.jar"
--config "C:\approved\dataai\customer-orders.json"

Set the working directory to the approved package/configuration directory.
Require return code 0; configure the task to fail on any other return code.
Use a timeout appropriate for the Spark job and configure SSIS cancellation according to customer policy.

3. Connect downstream SSIS work

Add a success precedence constraint from the Execute Process Task.
On success, use the customer's existing database/Spark connector to read clean rows, findings, profiles, or run outputs.
Do not assume that an output table exists when its name was blank in the DataAI configuration.
On failure, route to the customer's logging/notification task and retain the Spark application identifier.
Do not register the DataAI JARs in the GAC.

4. Validate the first run

Use an isolated source and dedicated output schema.
Require clean rows plus rejected rows to reconcile with source rows.
Inspect at least one intentionally failing rule.
Confirm every configured output table is overwritten only as intended by the quality CLI.
Test restart, retry, agent/service-account permissions, and SQL Agent scheduling separately.

5. Use advanced functions

The included Java libraries also provide analytics, time series, business and market functions, map readiness, insights, and matrix balancing. Those functions require a customer Java/Spark wrapper job. Invoke that wrapper from another Execute Process Task or the customer's Spark scheduler; they are not native SSIS transformations.

Usage is successful when: SSIS receives exit code 0, downstream tasks start only after Spark success, expected output tables reconcile, and failures follow the package's error path.