104 docs tagged with "BimlScript"

Eight practical tips for working with Biml in Visual Studio, including editor settings, directive placement, escaping characters, and learning paths.

Drive SSIS processing packages, including stored procedure execution and SSAS database processing, from a tiny metadata model that scales to many tasks.

Drive root level OleDb connections and per package connection references from a metadata table so generated SSIS packages all share the same connection managers.

Introduce BimlScript as a way to iterate over a package template and discuss why splitting a project across tiered Biml files makes sense.

Define BI assets such as tables in one Biml file, then iterate them in a second tiered file to generate truncate-and-load packages from RootNode metadata.

Drive an SSIS staging load from SAP using a metadata repository, the Theobald XtractIS source, and BimlScript that reads the SAP DD03L data dictionary.

Use the DataFactory, LinkedServices, Datasets, Pipelines, and Triggers tags in BimlStudio to generate Azure Data Factory v2 JSON from source metadata.

Drive a Biml package generator from header and detail metadata tables that describe a base SELECT and a sequence of Lookup transforms for dimensional denormalization.

Use BimlScript to generate one staging package per delimited flat file by reading file metadata from a SQL table.

Understand what Biml describes, how to install it, and the five small exercises that build a working foundation for SSIS automation.

Attach extra metadata to Biml objects with annotations and object tags, then read that metadata in higher tier files.

Walk through the first three exercises every Biml learner should complete: a blank package, a project connection, and a truncate-and-reload staging package.

Build a reusable BimlScript template that loads a Type 1 slowly changing dimension with hash key change detection and set based updates.

Script the SSIS OData source and OData connection manager in Biml using CustomSsisConnection and CustomComponent elements.

Overview of the Biml Package Generator feature in BIDS Helper, what it produces, and why script-driven Biml beats hand-built SSIS for repetitive packages.

Learn the structural rules of a Biml file, including root elements, plural collections, naming conventions, illegal characters, and whitespace handling.

Use the BimlExpress preview pane to see expanded Biml output without writing it to a file, including SQL output produced through code that uses GetDropAndCreateDdl.

BimlScript editor in BimlStudio

Survey the five kinds of code nugget that mix C# or VB into a Biml document and look at how text nuggets substitute computed values into the output.

Use control nuggets to add loops, conditionals, and external metadata calls that determine which Biml fragments are emitted.

Learn to automate staging layer creation across SSIS, ADF, Databricks, Snowflake, and Microsoft Fabric using BimlScript metadata-driven development.

How a metadata-driven Biml project replaces hand-built SSIS templates and keeps every package aligned with a single ETL framework.

Combine source metadata alerts, a Biml automation framework, and a simple SSIS testing harness to keep a data warehouse in sync with source systems still in development.

Define dimension and measure group metadata directly on the underlying tables, then assemble a multidimensional cube with role playing dimensions and partitions.

Drive SSIS package generation from a small metadata schema covering packages, connections, and connection types, then loop over the metadata to emit one package per row.

Add a custom C# extension method to the Biml object model so a BimlScript template can convert one node type into another with a single call.

Use AllDefinedSuccessors at a high tier to apply a default property such as DefaultBufferMaxRows to every Dataflow Task in a project.

Use template Biml objects and a high tier merge file to push template columns and template events onto every other table or package in BimlExpress.

Move shared C# or VB logic out of Biml files and into external code files using the code directive so the same helpers can drive many projects.

Convert helper methods in a Biml C# code file into extension methods so they can be called directly on Biml objects.

Reuse Biml across files and projects by moving common patterns into a callee file and invoking it with parameters from a caller file.

Discusses the three types of BimlScripts and which is right for the user's needs.

Generate an SSIS package that drops every table in a target database by looping over INFORMATION_SCHEMA until no tables remain.

Define ETL as relational metadata, then generate connection Biml from a stored procedure that returns connection name and connection string rows.

Extend a metadata-driven extraction package by emitting per-row containers with truncate, dataflow, and explicit source-to-target column mappings.

Generate one SSIS package per source table with BimlScript, then extend the script to apply per-table WHERE clauses from a metadata table.

How to create expandable transformers in BimlStudio

Generate the Project.params file for an SSIS Project Deployment model project from Biml by writing the parameter XML directly to disk during build.

Walk through the steps of authoring a small Biml file and expanding it into an SSIS package using the Biml Package Generator in BIDS Helper.

Generate CREATE TABLE statements for an entire staging environment by reading INFORMATION_SCHEMA from the source database and emitting an ExecuteSQL task per table.

Define tables in Biml, then use BimlScript to generate an SSIS package that runs the CREATE TABLE statements against the target database.

Define SSIS variables in Biml at different scopes and reference them from tasks and parameters within their containing scope.

Use default outputs and explicit InputPath elements to control how data flows from one component to the next in a Biml-defined SSIS dataflow.

When parallel SSIS packages from a Biml framework saturate the log writer with WRITELOG waits, enabling delayed durability on staging databases can cut total runtime in half.

Use ImportDB to materialize a SQL Server metadata schema as a DataSet inside BimlExpress, then drive connections, tables, and load packages from it.

Read a SQL Server meta schema with ImportDB, then emit a matching MetadataModel and MetadataInstance using the Biml Metadata feature in BimlStudio.

Use a custom metadata table to control which packages BimlScript generates and which optional transformations they include.

Define an Execute SQL Task in Biml that calls a stored procedure and captures the procedure's RETURN value into a package variable.

Use BimlScript to loop over every table in a source database, generate matching flat file formats and connections, and build a single package that exports each table to a CSV file in parallel.

Generate SSIS packages that split a large source table into multiple flat files using OFFSET FETCH and a Biml script that calls another Biml script.

Move Sage Sotamas 90 ProvideX data into SQL Server by generating destination tables from the vendor XSD, then producing one SSIS package per table with Biml.

Three ways to specify delimiters and text qualifiers on flat file definitions in Biml: the named enum, hex codes, and the literal character with proper encoding or escaping.

Generate an SSIS package that loops over every CSV file in a directory, imports each into a single SQL Server target table, and stores the file name with each row.

Use the BimlStudio command-line compiler to expand Biml files into SSIS packages outside the IDE so the build runs unattended on a schedule or in CI.

Use BimlScript to generate Azure Data Factory v1 input and output dataset JSON files for hundreds of source tables driven from a metadata spreadsheet.

Use BimlScript and a small C# loop to emit one Data Profiling Task per table in a source database, producing per-table profile XML files in a single SSIS package.

Use BimlScript to read a list of source tables from system metadata and emit one extract and load package per table from a single template.

Build SELECT statements from source metadata using the Biml GetColumnList method, including aliases, predicates, and delimiter overrides.

Use the global directive to inject shared content like template language settings, headers, namespaces, or includes into every Biml file at once.

Tag every Biml managed table with an extended property and let the generation template emit a drop step for any tagged table no longer in the metadata.

Build a reusable BimlScript template that loads a hybrid Type 2 (Type 6) dimension with both historical and current attributes.

Use GetQuerySchema to import column metadata from a SELECT query, view, table valued function, or stored procedure.

Use GetDatabaseSchema to pull schema, table, and view metadata from a source database into Biml so generated packages and SQL stay in step with the source.

Define the FlatFileConnection, FlatFileFormat, and FlatFileSource needed to read a delimited text file in an SSIS data flow generated from Biml.

Call GetDatabaseSchema on an ExcelConnection to materialize each worksheet as a Biml table and generate the matching staging DDL.

How to import tables using BimlScript in BimlStudio

Use the Biml include directive to copy shared markup from one file into another so common variables, tasks, or fragments live in a single place.

Preserve existing indexes on staging tables when generating drop and create DDL by importing the live target indexes back into the Biml table definition.

Use the TheobaldXtractSapConnection and TheobaldXtractSapSource elements to copy a single SAP table into SQL Server with one Biml file.

Build a single SSIS package from Biml using a connection, a control flow task, a data flow with a derived column, and an OLE DB destination.

Use CallBimlScriptWithOutput to return both Biml code and a dynamic object from a callee, so the caller can act on values produced inside the included script.

Use BimlScript to read worksheet names from an Excel workbook and emit one SSIS data flow per worksheet, all loading into the same target table.

Generate Biml flat file formats, connections, and matching staging tables from bcp generated XML format files using a small set of extension methods.

Use a topology sort extension method to walk foreign key dependencies and generate SSIS packages that recreate and load tables in the right order.

Extend the topology aware load pattern so independent branches run in parallel while still respecting foreign key dependencies.

Reduce mixed XML and C# complexity in Biml files by splitting into includes, using CallBimlScript, and pushing string assembly into T-SQL stored procedures.

Generate a master package and a child package with Biml that share an audit run id and a child execution id through parameter bindings.

Architecture overview for a metadata driven SSIS solution that uses Biml and BimlScript to generate table load packages and a master package from a source to target mapping table.

Drive Biml table generation from a small metadata table in the target database, attach the source SELECT as an annotation, and emit the create plus load packages.

Extend the metadata layer with multiple source connections and per table column lists, then carry the source connection and column choice through annotations into the load package.

Build SSIS staging packages from a flat file using Biml, then convert hardcoded values into a metadata driven generator that reads file format and column details from a database.

Generate Derived Column and Lookup transformations inside an SSIS data flow from rows in a transformation metadata model.

Split a copy-all-tables Biml solution into a driver file and a parameterized package callee so the per-table logic lives in one place.

Use a T-SQL bin packing routine and a BimlScript template to assign tables to balanced parallel containers and generate the matching SSIS package.

Avoid runtime errors when a Biml package uses the same custom script task more than once by giving each instance a unique ProjectCoreName.

Use BimlScript and ImportDB to reverse engineer table metadata out of an existing staging database into a metadata model that drives future Biml generation.

Compare the Biml definition of a table against the live database before recreating it so unchanged objects keep their data and indexes.

Build SSIS packages that ProcessUpdate every dimension in an SSAS database, either as a single processing task or as one task per dimension inside a parallel container.

Use AMO inside BimlScript to find the most recently processed partitions in each SSAS measure group and emit a Partition Processing task that targets only those partitions.

Generate SSIS packages that process every dimension, the most recent fact partitions, and cube indexes in an SSAS database from a single Biml script.

Why the OLE DB Destination in fast load mode silently truncates oversized values, what row by row mode does instead, and how to plan for it in a Biml driven project.

Use LINQ OrderBy, Skip, and Take inside BimlScript to chunk a large set of source tables into evenly sized groups, each loaded by its own SSIS package.

Pin GUIDs on project-level connection managers so regenerated packages keep their bindings to the connection managers they were generated against.

Tour the building blocks of a Biml file: text blocks, directives, control blocks, and the RootNode that ties multiple files together across compile tiers.

Use the ForEachAdoLoop control-flow task to iterate over a recordset variable and run multiple SQL tasks per row, with locally scoped loop variables.

Split a Biml solution across tiered files so that connections, packages, and orchestrators can be built in dependency order from a shared RootNode.

Build a reusable BimlScript template that loads a Type 2 slowly changing dimension using set based updates and hash key change detection.

Tag generated SSIS packages with Biml annotations and read those tags in later scripts to drive package grouping and master orchestration.

Use BimlScript to write one .sql file per table in RootNode.Tables containing the DROP and CREATE DDL for that table.

Drive multi-region SSIS deployment from a small set of metadata tables so the same source loads the same tables into per-region target databases.

Add file based logging to BimlScript executions inside BimlExpress by referencing the NLog assembly and writing structured messages to a log file.

Use the optionexplicit and optionstrict template attributes to write less verbose Visual Basic BimlScript when full type declarations are not needed.

Use a higher tier Biml file to capture the intermediate compiled XML produced after BimlScript expansion, making complex Biml files easier to debug.

An introduction to Biml and BimlScript covering what they are, why they exist, the tools that author them, the wider product ecosystem, and where Biml fits beyond SSIS.