Trace Lineage Through SQL Server Dynamic SQL

Many SQL Server load procedures never name their tables directly. They read a table name from a configuration table, glue it into a statement with +, load a file whose path arrives as a parameter, and run the result with EXEC or sp_executesql. A lineage tool that only reads the SQL text sees EXEC (@sql) and nothing else, so the real tables and files are missing from the lineage graph.

Starting with GSP Java 4.2.16, the data-flow analyzer works out what such statements do. This guide shows three abilities on one small procedure:

1. Runtime-determined table mapping. A parameter picks a row of a configuration table, and that row decides which tables the procedure uses.
2. Table names built by string concatenation. 'dbo.' + @table_name becomes dbo.orders.
3. File paths of BULK INSERT. The file a procedure loads becomes a node of the lineage graph, even when its path is a parameter.

GSP needs no database connection for this, and you do not have to change your SQL.

The example procedure

The procedure below loads one "feed" at a time. A small configuration table says, for each feed, which table it loads and which columns it copies.

-- The tables the procedure loads
CREATE TABLE stage.orders_raw    (order_id INT, customer_id INT, amount DECIMAL(10,2));
CREATE TABLE stage.customers_raw (customer_id INT, customer_name VARCHAR(100));
CREATE TABLE dbo.orders          (order_id INT, customer_id INT, amount DECIMAL(10,2));
CREATE TABLE dbo.customers       (customer_id INT, customer_name VARCHAR(100));
GO

CREATE PROCEDURE dbo.load_feed
    @feed_name VARCHAR(50),    -- which feed to load, for example 'orders'
    @file_path VARCHAR(260)    -- the file to import, for example 'C:\import\orders.csv'
AS
BEGIN
    BEGIN TRY
        -- A small configuration table: one row per feed
        CREATE TABLE #feed_config (feed_name VARCHAR(50), table_name VARCHAR(50), column_list VARCHAR(400));
        INSERT INTO #feed_config VALUES
            ('orders',    'orders',    'order_id, customer_id, amount'),
            ('customers', 'customers', 'customer_id, customer_name');

        DECLARE @table_name VARCHAR(50), @column_list VARCHAR(400), @sql NVARCHAR(MAX);

        -- 1. The parameter decides which row, and so which tables, are used
        SELECT @table_name = table_name, @column_list = column_list
        FROM #feed_config
        WHERE feed_name = @feed_name;

        -- 2 and 3. The table name is concatenated, the file path comes from a parameter
        SET @sql = 'BULK INSERT stage.' + @table_name + '_raw FROM ''' + @file_path + ''' WITH (FIRSTROW = 2)';
        EXEC sp_executesql @sql;

        -- 2. Table and column names are concatenated
        SET @sql = 'INSERT INTO dbo.' + @table_name + ' (' + @column_list + ') '
                 + 'SELECT ' + @column_list + ' FROM stage.' + @table_name + '_raw';
        EXEC (@sql);
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

-- How the procedure is called
EXEC dbo.load_feed 'orders', 'C:\import\orders.csv';

Neither stage.orders_raw nor dbo.orders appears in the procedure body, and neither does the file. They exist only at run time, once @feed_name has picked a row of #feed_config.

What GSP finds

Run the analyzer on the script (the Java program is in Analyze your own script below). GSP rebuilds both dynamic statements for the call on the last line and reports this lineage:

RESOLVED  BULK INSERT stage.orders_raw FROM 'C:\import\orders.csv' WITH (FIRSTROW = 2)
RESOLVED  INSERT INTO dbo.orders (order_id, customer_id, amount) SELECT order_id, customer_id, amount FROM stage.orders_raw
'C:\import\orders.csv'.C:\import\orders.csv  ->  stage.orders_raw.*
stage.orders_raw.order_id  ->  dbo.orders.order_id
stage.orders_raw.customer_id  ->  dbo.orders.customer_id
stage.orders_raw.amount  ->  dbo.orders.amount

Here is how the three abilities show up in the result:

• Table mapping. The call passes 'orders'. GSP evaluates the SELECT on #feed_config with that value, finds the orders row and uses its table name and column list. The customers feed does not appear, because this call never selects it.
• Concatenation. 'BULK INSERT stage.' + @table_name + '_raw ...' became BULK INSERT stage.orders_raw .... The column list also came from the configuration row, so every column gets its own lineage edge.
• File path. C:\import\orders.csv is a node of type path, and it feeds the whole staging table, stage.orders_raw.*. GSP does not guess which field of the file goes into which column.

The procedure body sits inside BEGIN TRY ... END CATCH; GSP evaluates the statements in the TRY block as usual.

When the script has no call

You will often have a procedure without the job that calls it. Delete the last line of the example (the EXEC) and run the analyzer again:

UNRESOLVED  BULK INSERT stage.orders_raw FROM '@file_path' WITH (FIRSTROW = 2)
    unknown @file_path: procedure parameter @file_path has no literal default and no call-site binding
RESOLVED  INSERT INTO dbo.orders (order_id, customer_id, amount) SELECT order_id, customer_id, amount FROM stage.orders_raw
RESOLVED  INSERT INTO dbo.customers (customer_id, customer_name) SELECT customer_id, customer_name FROM stage.customers_raw
stage.orders_raw.order_id  ->  dbo.orders.order_id
stage.orders_raw.customer_id  ->  dbo.orders.customer_id
stage.orders_raw.amount  ->  dbo.orders.amount
stage.customers_raw.customer_id  ->  dbo.customers.customer_id
stage.customers_raw.customer_name  ->  dbo.customers.customer_name

• With no value for @feed_name, GSP uses every row of the configuration table, so both feeds appear. For impact analysis this is what you want: every table the procedure can touch.
• The file path is a value only the caller knows. GSP marks that BULK INSERT as UNRESOLVED and names what is missing (@file_path), instead of making up a file name.

There are two ways to give GSP the missing values.

Add a call to the script. One EXEC line with typical values, like the last line of the example, is enough. GSP uses its values as SQL Server would.

Or preset the parameters in Java, without changing the SQL:

TSQLEnv presets = new TSQLEnv(EDbVendor.dbvmssql) {
    @Override
    public void initSQLEnv() {
    }
};
presets.setProcedureParameterValue("dbo.load_feed", "@feed_name", "orders");
presets.setProcedureParameterValue("dbo.load_feed", "@file_path", "C:\\import\\orders.csv");

DataFlowAnalyzer analyzer = new DataFlowAnalyzer(new File(args[0]), option);
analyzer.setSqlEnv(presets);
analyzer.generateDataFlow();

TSQLEnv is in the package gudusoft.gsqlparser.sqlenv; the other names are those of the program in Analyze your own script.

With these presets, the script without its call gives exactly the lineage of the first run. Name the procedure with its schema; a procedure created after USE SalesDB is SalesDB.dbo.load_feed. Presets take priority over the calls GSP finds in the script. If you give GSP database metadata in a JSON file, the same values can go into the procedure's parameterBindings entry.

Analyze your own script

Step 1. Collect the SQL. Put the procedure in a file. Add the CREATE TABLE statements of the tables it uses if you have them: they tell GSP the columns of each table. If you can, add a call with typical values, or plan to use presets.

Step 2. Run the analyzer. This program prints every dynamic statement GSP rebuilt, then the column-level lineage:

import java.io.File;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.dataflow.model.DynamicSqlSite;
import gudusoft.gsqlparser.dlineage.dataflow.model.Option;
import gudusoft.gsqlparser.dlineage.dataflow.model.UnresolvedFragment;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.relationship;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.sourceColumn;

public class DynamicSqlLineage {
    public static void main(String[] args) throws Exception {
        Option option = new Option();
        option.setVendor(EDbVendor.dbvmssql);
        option.setSimpleOutput(true);   // table-to-table lineage, without intermediate result sets

        DataFlowAnalyzer analyzer = new DataFlowAnalyzer(new File(args[0]), option);
        analyzer.generateDataFlow();

        // 1. Every EXEC of dynamic SQL, with the statement GSP rebuilt for it
        for (DynamicSqlSite site : analyzer.getDynamicSqlSites()) {
            System.out.println(site.getStatus() + "  " + site.getMaterializedText());
            for (UnresolvedFragment unknown : site.getUnresolvedFragments()) {
                System.out.println("    unknown " + unknown.getName() + ": " + unknown.getReason());
            }
        }

        // 2. The column-level lineage
        dataflow lineage = analyzer.getDataFlow();
        for (relationship edge : lineage.getRelationships()) {
            if (!"fdd".equals(edge.getType())) {
                continue;
            }
            for (sourceColumn source : edge.getSources()) {
                System.out.println(source.getParent_name() + "." + source.getColumn() + "  ->  "
                        + edge.getTarget().getParent_name() + "." + edge.getTarget().getColumn());
            }
        }
    }
}

Run it with the path of your SQL file as its argument. On the example it prints the output shown in What GSP finds. The argument can also be a directory: GSP then analyzes all its files together, and a call in one file gives values to a procedure defined in another. If the procedure's file starts with USE SalesDB, write the call with the database name, EXEC SalesDB.dbo.load_feed ...; with only dbo.load_feed, GSP cannot be sure the call reaches the same procedure and does not use it.

Step 3. Read the result.

• getDynamicSqlSites() lists every EXEC and sp_executesql of dynamic SQL. RESOLVED means GSP rebuilt the statement and its lineage is in the result. UNRESOLVED means part of the statement is unknown; getUnresolvedFragments() says which part and why. PARSE_ERROR means the rebuilt text is not valid SQL.
• Each fdd relationship is a column-level flow of data from its sources to its target. A source of type path is a file.
• For an UNRESOLVED statement, add a call or presets, or check the limitations below.
• DynamicSqlSite.isExecutionCertain() returns false when GSP cannot be sure the statement runs: it sits in a CATCH block, or behind a condition or loop GSP cannot decide. Its lineage is what the statement does when it runs.

Three Option settings matter here:

Setting	Default	Use it to
setAnalyzeDynamicSql(boolean)	true	Keep it on: it makes GSP analyze SQL built as a string.
setSimpleOutput(boolean)	false	true gives table-to-table lineage, as in this guide; false keeps every intermediate step.
setDynamicSqlCaseSensitiveCompare(boolean)	false	Text comparisons in a configuration query ignore upper and lower case and trailing spaces, as SQL Server's default collation does. Set true if your database compares case-sensitively.

Current limitations

• The configuration data must be in the script. GSP never connects to your database. It reads a #temp table or a table variable that the script fills with INSERT ... VALUES. If your configuration lives in a permanent table, GSP cannot know its rows: the dynamic statements stay UNRESOLVED, and the reason says so.
• Simple configuration queries only. GSP evaluates a SELECT from one configuration table with WHERE conditions (=, <>, <, >, <=, >=, IN, LIKE, IS NULL, AND, OR, NOT), TOP and ORDER BY. A query with a join, GROUP BY or another aggregate, a subquery, or a function GSP does not know is not evaluated. The statement stays UNRESOLVED instead of being guessed.
• Size limits. When there is no call or preset and one variable could take more than 8 values (for example, a configuration table with more than 8 feeds), GSP reports the statement as UNRESOLVED instead of listing every possibility. Add a call or a preset to analyze one feed. A configuration table with more than 1,024 rows is not evaluated. You cannot change these limits through Option yet.
• Up to four different calls. GSP evaluates at most four different calls of the same procedure. With a fifth, it says so on each statement (DynamicSqlSite.getDiagnostic()), leaves out the fifth call's values, and also lists every row of the configuration table, as if the procedure had no call, because it cannot tell what the remaining call selects.
• BULK INSERT lineage is table-level. The file feeds the whole table, as stage.orders_raw.* above. GSP does not read a FORMATFILE, so it does not map the fields of a file to columns. The DATA_SOURCE option appears only in the XML output, as the dataSourceName and uri of the path node.
• No placeholder file for a SET-built BULK INSERT without a call. In a procedure with no call and no presets, a BULK INSERT built with SET @sql = ..., as in the example, gives no file node. One built with SELECT @sql = ... gives a placeholder node named after the parameter, '@file_path'. Add a call or presets to get the real file in both cases.
• Simplified control flow. Values that change from one loop iteration to the next, GOTO, and conditions GSP cannot decide are evaluated in a simplified way. When GSP cannot be sure a statement runs, isExecutionCertain() says so.

Future work

• A placeholder file node for a SET-built BULK INSERT in a procedure without a call.
• Changing the size limits through Option.
• Under consideration: configuration queries that join tables or aggregate.

See also

• Column-level data lineage: the general guide to lineage with GSP.
• Data lineage options explained: setAnalyzeDynamicSql and the other dynamic SQL options.
• Release notes: the 4.2.16 entries describe these abilities in detail.