56 docs tagged with "framework"

Attaches a 'Description' annotation to an SSIS Package so the text shows up in the package properties pane and SSIS catalog metadata.

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.

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.

Declares an ADO.NET connection manager that uses the System.Data.SqlClient provider with Windows Integrated Security to talk to a SQL Server database.

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.

Webinar walkthrough showing how Biml fully generates a Data Vault data warehouse from source metadata.

The basic elements of a BimlScript file are covered.

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.

An introduction to transformers in BimlScript, a feature available in BimlStudio.

This webinar demonstrates BimlStudio Transformers, the "secret sauce" of Biml.

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.

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.

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.

These are the code samples from the PASS BI Virtual Chapter meeting from 2013/12/12. For the video, check out "Create and Load a Staging Environment...

Defines a 'DimProduct' Dimension with key/non-key attributes, attribute-to-attribute relationships, and a multi-level hierarchy ('Categories' rolling up Category, Subcategory, and ProductName), demonstrating how a Dimension references a Schema, which references a Database, which references the underlying Connection.

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.

This Walkthrough presents a very fast technique to build a simple (Truncate and Load) SSIS package by leveraging a SQL Stored Procedure and BIML.

How Biml extends an auto-generated Data Vault to handle business keys, satellite splits by fast-changing attributes, and per-attribute manual overrides driven from metadata.

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.

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.

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.

The ExecuteScript Script Project is a project we use to make shelling out and executing various processes easier.

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.

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.

Declares a generic File connection manager that points at a single file path with a 'CreateFile' usage so SSIS tasks can produce a new file at that location.

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.

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.

Declares an FTP connection manager with server, username, and password values so SSIS FTP tasks can transfer files to or from the remote host.

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.

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.

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.

Extend the BigData Twitter snippet so a single Biml package iterates over a configurable list of search terms.

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.

In this webinar we will go through the pattern required to migrate workloads to Azure. This session will be a live BimlScript demonstration with hardly...

Declares a Microsoft Message Queuing (MSMQ) connection that points at a private local queue, ready for use by SSIS Message Queue tasks.

Sample OLEDB connection

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.

A follow up on the power of using Transformers in BimlStudio

Calls 'ProjectManager.GetInstance().Project.AddNewPathToModel' via 'Dispatcher.Invoke' (because the operation runs on the UI thread) so a BimlScript can attach an existing file on disk to the current BimlStudio project.

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.

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.

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.

Learn how to 'roundtrip' your SSIS packages between SSDT and BimlStudio, and take advantage of the extended Biml toolsets.

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.

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.

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.

Declares an SMTP connection manager with SSL enabled, ready for use by an SSIS Send Mail task.

Uses '#@ include file=...#' to pull a FlatFileFormat fragment and an OleDB connection fragment out of separate files and into the parent Biml, so large definitions can be broken into reusable, readable pieces.

Declares a SQL Server Management Objects (SMO) connection to a SQL Server instance so SSIS Transfer Database, Transfer Logins, Transfer Jobs, and similar management tasks can manipulate server-level objects.

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.

Transformer that will insert transformations before component.

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.

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.

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.

Declares a Windows Management Instrumentation (WMI) connection against the local machine's root\cimv2 namespace with Windows authentication, ready for use by SSIS Web Service Management or WMI Data Reader tasks.

This walkthrough demonstrates how to create your own custom errors, warnings, and messages. It also shows how to interact with the validation system to...