Skip to main content

BimlScript Snippets

Reusable BimlScript code snippets organized by topic for quick reference and copy-paste use.

Connections

Defining and configuring connection managers for relational, file, web, and management endpoints.

  • ADO.NET Connection: Declares an ADO.NET connection manager that uses the System.Data.SqlClient provider with Windows Integrated Security to talk to a SQL Server database.
  • Active Directory ADO.NET Connection Manager: Creates an ADO.NET connection manager that uses the .NET Providers for OLE DB / OLE DB Provider for Microsoft Directory Services to talk to a domain controller.
  • Analysis Services Connection: Declares an AnalysisServicesConnection that targets an SSAS multidimensional cube through the MSOLAP provider with Impersonate authentication, ready for use by Processing or Execute DDL tasks.
  • Cache Connection: Declares a RawFileFormat with three columns and pairs it with a CacheConnection so a Cache Transform can persist lookup data as a typed raw file for reuse across packages.
  • Excel OLEDB Connection: Declares an ExcelConnection that targets an .xlsx workbook through the Microsoft ACE OLE DB 12.0 provider with HDR=YES so the first row is treated as column headers.
  • FTP Connection: Declares an FTP connection manager with server, username, and password values so SSIS FTP tasks can transfer files to or from the remote host.
  • File Connection: Declares a generic File connection manager pointed at a single file path with 'FileUsageType=CreateFile' so SSIS tasks can produce a new file at that location.
  • Flat File Connection: Declares a tab-delimited FlatFileFormat with nine typed columns and pairs it with a FlatFileConnection that references the format by name, giving SSIS sources and destinations a complete description of the file.
  • HTTP Connection: Declares an HTTP connection manager configured with a client certificate, server URL, domain, and credentials so SSIS Web Service tasks or script components can call an HTTPS endpoint.
  • MSMQ Connection: Declares a Microsoft Message Queuing connection that points at a private local queue, ready for use by SSIS Message Queue tasks.
  • OLEDB Connection: Creates an OLE DB connection to a SQL Server database using the SQL Server Native Client 10 (SQLNCLI10) provider with Windows Integrated Security.
  • SMTP Connection: Declares an SMTP connection manager with SSL enabled, ready for use by an SSIS Send Mail task.
  • SQL Server Management Connection: Declares a SQL Server Management Objects (SMO) connection so SSIS Transfer Database, Transfer Logins, Transfer Jobs, and similar management tasks can manipulate server-level objects.
  • WMI Connection: Declares a Windows Management Instrumentation (WMI) connection against the local root\cimv2 namespace with Windows authentication, ready for use by SSIS WMI Data Reader or WMI Event Watcher tasks.

Tables, Schemas & Metadata Imports

Reading source database schemas with ImportDB and INFORMATION_SCHEMA, generating per-table packages, and emitting CREATE TABLE scripts.

  • Biml Export to Flat File: Reads INFORMATION_SCHEMA from a SQL Server source to generate a per-table FlatFileFormat, FlatFileConnection, and OLE DB to FlatFileDestination dataflow, producing one delimited extract per base table in the chosen schema.
  • Copy All Data from Live Source Database: Connects directly to a live source database with 'ImportDB', then generates one Dataflow task per imported table that copies the data from the source connection to the target connection, with no advance knowledge of the source schema required.
  • Extract All Tables: Iterates 'RootNode.Tables' to emit one extract package per table (OleDbSource SELECT * to OleDbDestination, Source to Target connection) plus a driver package that chains all generated packages together with ExecutePackage tasks.
  • Fast Targeted Table Import (BimlStudio Preview): BimlStudio preview script that imports one or more named tables from a given database, walks every schema looking for the match, writes the resulting Biml under the database's 'Tables' directory, adds the file to the live project, sets the schema to the database default, and calls a follow-up script to add UsedByCycle tags.
  • Find Tables Missing Metadata Columns: Validation BimlScript that walks 'RootNode.Tables' and reports any table missing the project's required audit/metadata columns, with optional reporting of passing tables, so the check can run automatically before each build.
  • Finish Table Import Script: BimlStudio script that finishes a freshly imported Table: resets the schema to the database's default, renames the Biml file to local convention, moves it to the database's Tables directory, and calls a follow-up script to add UsedByCycle tags.
  • Generate a Staging Load Package from a View: Reads INFORMATION_SCHEMA for a configurable source view to optionally drop and recreate the destination table, then emits an SSIS package that truncates the staging table, optionally drops and rebuilds its indexes via stored procedures, and loads it from the view via an OLE DB source-to-destination dataflow with FastLoad.
  • Import Database Assets into Biml with ImportDB: Calls 'AstOleDbConnectionNode.ImportDB()' from a class feature control block, then re-emits the imported Schemas and Tables as Biml via 'GetBiml()' so the model mirrors the live database.
  • Import a Filtered List of Tables Using a Static List of Table Names: Calls 'ImportDB' against a live source, then filters the returned TableNodes against either a hard-coded C# list or a metadata-driven SQL query, a more flexible alternative to 'ImportDB''s LIKE-style filter when you need to pick disparate table names.
  • Initial Historical Load into SQL Server PDW (APS): Lists every base table from sys.tables and emits a per-table SSIS dataflow that pumps rows through the 'SqlServerPdwDestination' adapter, the path used for an initial historical load when migrating to SQL Server Parallel Data Warehouse / Analytics Platform System.
  • Use GetTableSql() to Generate CREATE TABLE Scripts: Walks 'RootNode.Tables', 'RootNode.Dimensions', and 'RootNode.Facts' and emits one ExecuteSQL task per modeled object that runs its 'GetTableSql()' DDL against the table's connection, so the resulting SSIS package builds out the target schema from your Biml model.

Files & Flat-File Formats

Declaring FlatFileFormat definitions and generating per-table flat-file imports and exports.

  • CSV Files Import Automation: Two paired BimlScripts that scan a folder of delimited files, read each header row, then generate matching SQL staging tables and per-file SSIS import packages (flat file connection, file format, and dataflow) for every file in the directory.
  • Extract a Stored Procedure Resultset to CSV or Excel: Configurable BimlScript that introspects a stored procedure resultset using SET FMTONLY ON, then generates an SSIS package that runs the procedure and writes the output to either a timestamped CSV file or an Excel workbook, including a script task that injects 'GETDATE()' tokens at runtime and a generated CREATE TABLE for the Excel sheet.
  • Flat File Format: Declares a standalone FlatFileFormat asset with typed columns and per-column delimiters that can be referenced by one or more FlatFileConnection objects.
  • Generate CSV File Format from Database: Reads a SQL Server schema's tables and column types, then emits one package per table with a matching FlatFileFormat and FlatFileConnection whose typed columns mirror the destination table, so flat-file imports get data-type validation at the connection manager.

Dataflow Components & Sources

Script Component sources and transformations, the UnionAll component, the CacheTransform, and dataflow-level error handling.

  • Add a Row Number Column to a Flat File Export: Adds a sequential row-number column to a flat-file export by piping an OLE DB source through a Script Component transformation that increments a counter for every row, so downstream business users have a stable line reference when reporting issues with the file.
  • Add a Row Number Column with a Script Component Transformation: Loops over every user table in a SQL Server source and emits one extract package per table; each package reads the current max(rownumber) from the destination, runs the source through a Script Component transformation that adds an incrementing 'rownumber' column, and writes it to the destination table.
  • Combine Error Outputs with a Union All: Wires the 'Error' outputs of a DataConversion and an OleDbDestination into a single UnionAll, drops unused columns from one of the inputs via 'IsUsed=false', and writes the merged failed rows to a flat file destination.
  • Create and Populate a Persistent Lookup Cache: Defines a RawFileFormat, a File connection for the cache file, and a CacheConnection bound to both, then uses a CacheTransform in a dataflow to populate the cache so later Lookup components can reuse it across packages.
  • Creating a ScriptComponentProject: Builds a non-synchronous ScriptComponentProject that consumes an OData service via 'System.Data.Services.Client', pages through results with Skip/Take, and emits one output row per record into a typed OutputBuffer.
  • MD5 Script Component: Drops an inline ScriptComponent transformation into a dataflow that concatenates row values and emits an MD5 hash column, useful when policy forbids open-source hashing components or you need a pure-.NET alternative to T-SQL HASHBYTES.
  • Reading a Twitter Feed with a Script Component: Calls the legacy Twitter search Atom feed from a custom ScriptComponentSource using a 'SearchTerm' package variable, then writes new tweets above the last-seen TweetNumber to a 'dbo.TwitterLog' table via OleDbDestination.
  • Reading an RSS Feed with a Script Component: Pulls RSS items from a configurable feed URL via a custom ScriptComponentSource, runs them through a Lookup against an existing log table to filter out items already loaded, and persists new entries into 'dbo.RSSFeedReader' via an OleDbDestination.
  • SHA1 Script Component Driven by Table Metadata: Imports a filtered list of AdventureWorks tables, then generates one per-table extract package whose script component reads each column's runtime DataType from the pipeline buffer, serializes the value (including null-handling tokens) into a byte array, and emits a SHA1 hash as a new output column.
  • Setting Custom Error Handling Behavior: Demonstrates the 'ErrorHandling' element on a dataflow component, including how to set output-level 'ErrorRowDisposition' and 'TruncationRowDisposition' defaults and override them per column.
  • Union All from Two SQL Sources to a Flat File: Reads 'SurveyResponses' and 'JuniorSurveyResponses' from a SportsSurvey OLE DB source, merges them with a UnionAll transformation, and writes the combined rows to a semicolon-delimited flat file via a FlatFileFormat and FlatFileConnection.

Tasks & Control Flow

Control-flow tasks, package execution, master-package orchestration, and SSIS task wiring.

  • Build a Master Package That Executes Every DTSX in a Directory: Walks a directory (recursively or top-only) for '.dtsx' files, sourcing the root path from a 'PROJECT_ROOT' environment variable with a hard-coded fallback, then emits one ExecutePackage task per file inside a single master package.
  • Call a Web Service from a WebService Task: Builds a package with an HttpConnection pointing at a SOAP WSDL endpoint and a FileConnection for the response, then runs a WebService task that calls the 'GetQuote' method with a stock-symbol variable parameter and writes the returned XML to disk.
  • Control Flow Precedence Constraints: Three sample packages that walk through the main PrecedenceConstraint patterns: Linear ConstraintMode with no explicit constraints, Parallel ConstraintMode with EvaluationValue defaults and LogicalType='Or' fan-in, and a constraint driven by EvaluationOperation='Expression' against a package variable.
  • Create Package to Run All Packages in a Directory: Generates a master package that runs every '.dtsx' file in a given directory in parallel as ExecutePackage tasks, the standard pattern for orchestrating a load phase produced by a per-table generator.
  • Embed a Script Task Project Inline: Embeds a complete ScriptTaskProject (assembly references, ReadOnlyVariables, ReadWriteVariables, and 'ScriptMain.cs') directly inside a Script task instead of declaring it under a shared 'ScriptProjects' root, which keeps automation-generated packages self-contained.
  • ExecuteScript Script Project: A reusable SSIS Script Task project that shells out to PowerShell, Perl, C# (cs-script), Cygwin bash, or standard Windows executables, captures merged stdout/stderr to a log file, supports automatic retries, and parses SSIS_VARIABLE tokens from script output to write values back into package variables.
  • Executing a Local Process from SSIS: Runs a local executable via an ExecuteProcess task, captures its stdout into the 'User::ProcessOutput' variable, and uses a property expression to build the 'Arguments' string at runtime from the current date in DD-MM-YYYY form.
  • Send a Failure Notification with a Send Mail Task: Wires a Send Mail task to an SMTP connection (SSL enabled) so a package can email a high-priority failure notice to an admin from a notifier package.
  • Transfer SQL Server Objects Between Databases: Wires a TransferSqlServerObjects task to a SqlServerManagementConnection so a package can copy a hand-picked list of tables and their data between two SQL Server databases, dropping the destination objects first when they already exist.

Parameters & Variables

Package and project parameters, parameter bindings, and runtime configuration through SSIS variables.

  • Execute Package Task with Package Parameters: Builds a parent SSIS package whose ExecutePackage task binds one of its variables to a child package parameter via ParameterBindings, then shows the child consuming that parameter as a query parameter in an Execute SQL task.
  • Package Parameters: Declares typed package parameters under a 'Parameters' element and references them from inside the package via '@[$Package::ParameterName]' expressions, the SSIS 2012+ replacement for older package configurations.
  • Pass a Dynamic SQL Query to an ADO.NET Source: Works around the absence of 'SqlCommandVariable' on the ADO.NET source by setting the source's 'SqlCommand' property at runtime through an expression bound to a package variable, so the same ADO.NET source can run incremental queries against MySQL or any other ADO.NET provider.
  • Referencing Project and Package Parameters: Declares two project parameters on a PackageProject and two more on the package itself, then references them from an Execute SQL task via both '@[$Project::...]' and '@[$Package::...]' expressions and via fully scoped and short-name parameter bindings.
  • Setting the Connection String in a Package from a Variable: Binds a connection's ConnectionString property to a package variable through a property expression and pairs it with a 'ParentPackageConfiguration', so a calling parent package can push a runtime connection string into the child.
  • Setting the XSD File Path of an XML Data Source from a Variable: Declares two FileConnections (one for the XML file, one for the XSD schema) and binds the XSD connection's ConnectionString property to a package variable via an expression, so the schema path can be supplied at runtime.
  • Using Execute SQL Parameters: Binds package-variable inputs to '?' placeholders in an Execute SQL task's DirectInput query through its 'Parameters' collection, the standard pattern for passing values into parameterized SQL via OLE DB or ODBC connection types.

Custom & Third-Party Components

SSIS custom components and tasks from Microsoft, COZYROC, and Konesans, plus generic CustomComponent and CustomTask use.

  • Balanced Data Distributor by Microsoft: Wires Microsoft's Balanced Data Distributor (BDD) custom component into a dataflow so a single OLE DB source feeds two parallel OLE DB destinations writing to the same table, splitting rows round-robin via two BDD output paths in an ExclusionGroup.
  • Flow Synchronization Component by COZYROC: Wires two parallel dataflows through the third-party COZYROC Flow Synchronization custom component so both dataflows advance in lock-step at the same throughput, sharing a named semaphore and a MaxDelta buffer threshold.
  • Lookup Plus Component by COZYROC: Uses two instances of the third-party COZYROC Lookup Plus component to enrich rows in a dataflow, the first using a hard-coded reference query and the second using a query stored in a package variable, then routes the result to a Konesans Trash Destination.
  • Reference a Third-Party Custom Component with CustomComponent: Pulls three columns from AdventureWorks Person.Address through the SSIS Multiple Hash custom component declared via 'CustomComponent', then writes the source columns plus the new HashedColumn output into a destination table on SSIS 2012.
  • Trash Destination Component by Konesans: Terminates a dataflow path with the Konesans Trash Destination, letting you exercise an SSIS dataflow during development without actually writing the data anywhere.
  • Zip Task by COZYROC: Adds the third-party COZYROC Zip task to a package via 'CustomTask', configuring it to compress a single source file connection to a destination .zip file connection using a Zip-format ObjectData payload.

Transformers

BimlStudio Transformers for bulk template changes across packages, tables, and dataflows.

  • Setting Variable Values Using Transformer BimlScript: Uses an expression control block inside a transformer to set package variable values from properties of the TargetNode and from annotation lookups via 'GetTag(string)', so the same transformer can populate per-table SchemaName, TableName, and PackageName variables across many packages.
  • Transformer Targeting Multiple SSIS Versions: Demonstrates a Package-level transformer that injects an 'OnPreExecute' logging pattern and branches its Biml emit on 'CompilerSettings["SsisVersion"]', so the same framework template produces SSIS-2008 R2 log providers or SSIS-2012 system-variable bindings depending on the target.
  • Transformer to Add Default Audit Columns to Every Table: A LocalMerge Table transformer that injects a standard set of audit columns onto every target Table without modifying the original Biml definitions.
  • Transformer to Check If a Column Exists and Add If Not: Extends the add-default-columns transformer pattern with a 'GetColumnList()' membership check so the transformer only emits new Column definitions when they are not already present, avoiding duplicate-column errors when the same transformer runs across tables that already have some of the columns.

Reuse & Modularization

Include directives, CallBimlScript, dynamic Biml modules, and BimlStudio scripting APIs.

  • Dynamic Biml Module Called with CallBimlScript: A parameterized child BimlScript (TableName, AuditTableName) that emits a per-table container with an Execute SQL task running a metadata-driven stored procedure plus an audit-log insert, called from a parent script via CallBimlScript to assemble fact and dimension load packages from metadata.
  • Generating a List of Biml Scripts and Descriptions: Enumerates every '.biml' file in a directory and extracts the 'annotation' element tagged 'Summary' from each file's text, formatting the results as a Confluence wiki table so a team can publish a self-updating catalog of their reusable scripts.
  • Programmatically Add a File to a BimlStudio Project: Calls 'ProjectManager.GetInstance().Project.AddNewPathToModel' via 'Dispatcher.Invoke' so a BimlScript can attach an existing file on disk to the current BimlStudio project.
  • Run Every Validation Script in a Directory: Enumerates every Biml file in a configured framework directory whose name matches the 'Val_*' pattern, calls each one via 'CallBimlScript', and inlines the captured output as XML comments so a single Preview executes the whole validation suite.
  • Split Large Biml Files with the Include Directive: Uses the '#@ include file=...#' directive to pull a FlatFileFormat fragment and an OLE DB connection fragment from separate files into a parent Biml file, so large definitions can be broken into reusable, readable pieces.

Patterns & Performance

End-to-end ETL patterns and performance tuning techniques.

  • Add a Description to a Package: Attaches a 'Description' annotation to an SSIS Package so the text shows up in the package properties pane and SSIS catalog metadata.
  • Creating a Dimension Modeled by Connection, Database, and Schema: Defines a 'DimProduct' Dimension with key/non-key attributes, attribute-to-attribute relationships, and a multi-level Categories hierarchy, demonstrating how a Dimension references a Schema, which references a Database, which references the underlying Connection.
  • Disable Then Rebuild Non-Clustered Indexes Around a Dataflow: Queries sys.indexes for a target table, captures DISABLE and REBUILD ALTER INDEX statements into an object variable, then uses ForEachAdoLoop containers to disable every non-clustered index before a dataflow and rebuild them afterwards for faster bulk loads.
  • Incremental Load Design Pattern: Implements the brute-force incremental-load pattern from the book 'SSIS Design Patterns': a Lookup classifies source rows as new versus existing, a Conditional Split detects changed non-key columns, new rows go straight to the destination, and changed rows stage to a temp table that a follow-on Execute SQL applies with a set-based UPDATE.
  • Pick a Dataflow at Runtime Based on the File Header: Reads the first line of a delimited file as a single 'FileInfo' string, routes it through a Conditional Split that compares against known header signatures, counts rows on each branch, then uses a precedence-constraint expression on a non-zero row count to fire the matching per-layout Dataflow task.