Package generation with SSIS – an overview

In spite of the fact that you can hardly ignore BIML (with bloggers like Cathrine Wilhelmsen and Bill Fellows sharing great stuff), there are then again other alternatives for bundle generation in SSIS. While getting a charge out of a little conversation on LinkedIn about the computerization choices, I out of nowhere remembered as yet having a slidedeck from a presentation I gave early 2014.

Kindly note that these are my early perspectives on Biml, dating from end-2013. Kindly don’t put together any choices with respect to this: things are obsolete (BimlExpress presently provides free punctuation featuring for BimlScript inside Visual Studio; BimlT records can alter existing bundles) and innovation descriptions may not be 100% accurate (nothing confirmed by Varigence)

The presentation was about SSIS 2012, so not everything will work a similar path in SSIS 2014+. For instance: from what I’ve heard, EzAPI doesn’t have support for SQL Server 2014+. I’m intending to write an update shortly, however in the mean time this may prove valuable for gathering a significant level overview. SO, you should learn MSBI Course to understand it

Several products are available for Data Warehouse computerization in SQL Server. Models are Quipu (Data Vault), LeapFrogBI (Dimensional Model) and BI Accelerator (in spite of the fact that they haven’t posted anything since 2012). In addition, practically all BI consultancy firms appear to have built up their own extravagant ETL/DWH generator. I will not digress into these products, yet center around the frameworks you can use to create these computerization assignments all things being equal.

In short, there are three (four) notable approaches to create SSIS-bundles programatically:

SSIS Automation API

EzAPI

BIML

(Writing out SSIS bundles directly – which has gotten fairly easier starting with SSIS 2012)

SSIS Automation API utilizes the SSIS Object Model to perform assignments in bundles – AFAIK this is the thing that Visual Studio utilizes in the background to create your bundles (which doesn’t imply that everything that Visual Studio does is available through the API – things like distinguishing segment names and document structures aren’t, for instance). You can get to the SSIS Automation API and write code against it in .Net (C# or VB).

Writing code in the Automation API isn’t the most modern method of interacting with an API. While the Task Flow is as yet readable, dealing with Data Flows feels very cumbersome..

Creating a Task Flow utilizing SSIS Automation API

Creating a Task Flow utilizing SSIS Automation API

Creating a Data Flow utilizing SSIS Automation API

Creating a Data Flow utilizing SSIS Automation API

The reason Data Flows are so hard, is that we’re working directly with COM protests here:

IDTSComponentMetaData100 objective = dataFlowTask.ComponentMetaDataCollection.New();

destination.ComponentClassID = “DTSAdapter.OleDbDestination”;

CManagedComponentWrapper destDesignTime = destination.Instantiate();

destDesignTime.ProvideComponentProperties();

The SSIS Automation API can be utilized for generating new bundles just as altering existing bundles. Everything occurs in code (C#/VB.Net), yet the correspondence is fairly obsolete: you need to utilize COM classes.

EzAPI is a wrapper for the SSIS Automation API. The main thing EzAPI does is providing .Net wrappers for some classes, so you don’t have to write this COM-taking care of (code inexactly dependent on Paul Rizza’s intro to EzAPI):

/Create bundle and association managers:

var bundle = new EzPackage();

var srcConn = new EzOleDbConnectionManager(package) {

Name = “SRC Conn”,

ConnectionString = “Data Source=localhost;Provider=sqlncli11;Integrated Security=sspi;Initial Catalog=AdventureWorks2012”

};

var destConn = new EzOleDbConnectionManager(package) {

Name = “DST Conn”,

ConnectionString = “Data Source=localhost;Provider=sqlncli11;Integrated Security=sspi;Initial Catalog=MSBIP_EZPI_Extract”

};

/Control Flow:

var truncateTableTask = new EzExecSqlTask(package) {

Name=”EST Truncate Table”,

Association = srcConn,

SqlStatementSource = “TRUNCATE TABLE ” + destinationTableName

};

var dataFlowTask = new EzDataFlow(package) { Name = “DFT Extract” };

dataFlowTask.AttachTo(truncateTableTask);

/Add Data Flow components:

EzOleDbSource source = Activator.CreateInstance(typeof(EzOleDbSource), new object[] { dataFlow }) as EzOleDbSource;

source.Connection = srcConn;

source.Name = “Source”;

source.Table = “SourceTable”;

EzOleDbDestination Dest = Activator.CreateInstance(typeof(EzOleDbDestination), new object[] { dataFlow }) as EzOleDbDestination;

Dest.Connection = destConn;

Dest.Name = “Objective”;

/Connect the Data Flow:

Dest.AttachTo(source, 0, 0);

Dest.Table = “DestinationTable”;

Dest.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;

Dest.SetComponentProperty(“FastLoadMaxInsertCommitSize”, 100000);

This is a lot easier to read than the original Automation API: this seems as though plain .Net-style, offering all pleasures like declarative style programming.

EzApi can likewise be utilized both for generating new bundles just as altering existing bundles. You can write code in C# or VB.Net, and do as such while utilizing regular .Net-like constructions for the SSIS protests as well.

BIML represents BI Markup Language. It is a domain-explicit XML to generate bundles. I heard someone once say it utilizes EzAPI, however I find that fairly improbable.

Every SSIS-object is a XML component in BIML, which gives an exceptionally declarative approach to characterize how your bundles ought to be generated: all settling in SSIS (foreach containers, data streams) is displayed settled in BIML as well:

<Biml xmlns=”http://schemas.varigence.com/biml.xsd”>

<Packages>

<Package ConstraintMode=”Parallel” Name=”Voorbeeld”>

<Tasks>

<Dataflow Name=”Import CSV”>

<Transformations>

<FlatFileSource ConnectionName=”CSV association” Name=”Read CSV” FileNameColumnName=”filename”/>

<DerivedColumns Name=”DC Audit info”>

<Columns>

<Column Name=”Load_DateTime” DataType=”DateTime”>@[System::StartTime]</Column>

</Columns>

</DerivedColumns>

<DataConversion Name=”Convert Project_naam to fixed length”>

<Columns>

<Column SourceColumn=”project_naam ntext” DataType=”StringFixedLength” Length=”50″ TargetColumn=”project_naam”/>

</Columns>

</DataConversion>

<OleDbDestination ConnectionName=”DB” Name=”DB Write to table”>

<ExternalTableOutput Table=”[schema].[table]”/>

</OleDbDestination>

</Transformations>

</Dataflow>

</Tasks>

</Package>

</Packages>

</Biml>

You can run this BIML script from inside SQL Server Data Tools, which – if no errors are found – will result in a brand new SSIS bundle inside the project where you added your BIML record. You simply need to have the free and great module BIDS Helper introduced – which you ought to have in any case.

In itself, BIML is static. So out of the BIML above, precisely one kind of SSIS bundle can be generated. The clever straightforwardness and exceptionally descriptive method of expressing the displaying of your bundle in BIML takes into consideration simple BIML generation however, twoly:

Generate BIML from another script (like generating XML)

Use BIMLScript

Generating BIML from another script isn’t referenced regularly, yet isn’t excessively hard. You can without much of a stretch use string formats to insert dynamical data in a BIML layout, for instance:

<OleDbConnection Name=”DB” ConnectionString=”Data Source={4};Initial Catalog={3};Provider=SQLNCLI11;Integrated Security=SSPI;Connect Timeout=30;” DelayValidation=”true”>

Seen from a significant level, what will happen is this:

Biml-templatingBad thing: you have this ‘in the middle’ BIML-script, and need to execute two separate processes: one for the string mappings, and one for the BIML to DTSX arrangement (I’ve tried to address the BIML motor directly, however haven’t succeeded up until now).

Utilizing BIMLScript is the ‘local’ BIML route for computerizing things. It resembles an ASP or PHP way of generating site pages:

<FileFormats>

<FlatFileFormat Name=”CSVFormat” RowDelimiter=”CRLF” ColumnNamesInFirstDataRow=”true” IsUnicode=”false” TextQualifer='”‘>

<Columns>

<# foreach(KeyValuePair<string, string> segment in segments) {{ #>

<Column Name=”<#=column.Key#>” DataType=”AnsiString” Length=”500″ Delimiter=”<#=column.Value#>”/>

<# }} #>

</Columns>

</FlatFileFormat>

</FileFormats>

The critical difference with ASP or PHP is that the ‘in the middle’ product (to be specific, the ‘assembled’ BIML script) isn’t visible1:

BIMLScript

While linguistic structure featuring and auto-fruition in SSDT works perfect for BIML (as long as you have introduced BIDSHelper), it doesn’t work for BIMLScript. This makes it hard to troubleshoot BIMLScript, in light of the fact that you don’t see the in the middle of BIML code, however the BIML compiler’s error messages are about precisely this part (which is imperceptible to you). The developers of BIML, Varigence, have an answer for that

Fog is Varigence’s advancement environment for BIMLScript. It’s anything but a free apparatus, yet has a rather impressive feature list (look at it here). It makes the improvement of BIMLScript a lot easier in light of the fact that you have punctuation featuring and see what’s causing errors on your screen.

Altering BIMLScript in SSDT

Altering BIMLScript in SSDT

Altering BIMLScript in Varigence Mist

Altering BIMLScript in Varigence Mist

I have utilized BIMLScript now and again in a sort of in the middle of way, by utilizing PowerShell to generate BIMLScript, which incorporated to SSIS bundles. Albeit this sounds cumbersome, neither my customer nor my employer in those days needed to purchase Mist, and building up all rationale inside BIMLScript caused a lot of errors for me.

At long last remember that everything remains SSIS – with all provisos, and ‘features’ of SSIS still set up. Additionally, remember that BIML overwrites existing articles inside your project. One issue that I confronted was the way that my project association managers were overwritten, and my project parameters didn’t work. In any case, that was early 2014 – odds are high that this is fixed meanwhile

In this article

Join the Conversation