BimL – Intro(Truncate and Load)

This will be an ongoing tutorial working with T-SQL, SSIS, BimL, and C#. The primary purpose of this series will be to first introduce you to some of the formal concepts of Biml such as configuring Packages, Connections, and Tasks. In future blogs we will learn how to create reusable Biml files for SSIS Design Patterns.

Tools you need:

  1. Visual Studio
  2. BIDS Helper BIML Express– At the current time BIDS Helper is not working for Visual Studio 2015, I am using Biml Express which is an add-on to Visual Studio which can be found here BIML Express.
  3. Adventureworks 2016 Truncate and Load – Biml

First lets create 2 destination tables in the AdventureworksDW2016 database: Dest_Address and Dest_Person. *No Data warehouse concepts here yet, we just want to get a clear picture of how Biml is working.

CREATE TABLE [dbo].[Dest_Person](

[ID] [INT] IDENTITY(1,1) NOT NULL,

[FirstName] VARCHAR NULL,

[LastName] VARCHAR NULL,

[MiddleInitial] VARCHAR NULL,

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Dest_Address](

[ID] [INT] IDENTITY(1,1) NOT NULL,

[AddressLine1] VARCHAR NULL,

[AddressLine2] VARCHAR NULL,

[City] VARCHAR NULL,

[State_Abbreviation] VARCHAR NULL,

[Zip] VARCHAR NULL,

CONSTRAINT [PK_Dest_Address] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

*There will be scenarios where it is more applicable(but not always) to Truncate and Load a destination table versus applying Delta changes (Inserts, Updates, Deletes). Lets create our first Biml file which later we can generate our SSIS package from. We do so by right clicking on our Project and choosing “Add New Biml File”. It is then created under the Miscellaneous folder as BimlScript.biml

1.) Connections You start by typing a < which is the opening of a segment of code and opens Biml’s Intellisense. Every segment must begin and end with < and >.

1.) Connections

You start by typing a < which is the opening of a segment of code and opens Biml’s Intellisense. Every segment must begin and end with < and >.

  Choose Connections and then Connection – where we will define our source and destination. Here we have defined two connections. We gave them a Name and a ConnectionString

 

Choose Connections and then Connection – where we will define our source and destination.

Here we have defined two connections. We gave them a Name and a ConnectionString

2.) Packages

2.) Packages

From here we will be able to generate an actual package. We are giving the package a name and defining “Parallel” as our ConstraintMode. This of course means Tasks inside our package will be ran in Parallel instead of Linear. We will dive into that at a later time. Go ahead and Generate the package by right clicking the Biml file and hitting Generate SSIS Packages. Notice we will not see any connections, that’sbecause we need to associate a connections with this package, lets do that! Since we are still in the Control Flow all we need is the Dest connection because this is where our Truncate SQL Statement will be. Once we get into the Dataflow that’s where we will have our Source and Dest. Also we do not need to redefine the whole connection string either, just associate it by typing: <Connection ConnectionName=”Dest”></Connection>

From here we will be able to generate an actual package. We are giving the package a name and defining “Parallel” as our ConstraintMode. This of course means Tasks inside our package will be ran in Parallel instead of Linear. We will dive into that at a later time.

Go ahead and Generate the package by right clicking the Biml file and hitting Generate SSIS Packages. Notice we will not see any connections, that’sbecause we need to associate a connections with this package, lets do that!

Since we are still in the Control Flow all we need is the Dest connection because this is where our Truncate SQL Statement will be. Once we get into the Dataflow that’s where we will have our Source and Dest. Also we do not need to redefine the whole connection string either, just associate it by typing:

<Connection ConnectionName=”Dest”></Connection>

Go ahead and re-generate the SSIS package to see you now have a Dest connection on your Controlflow.

Go ahead and re-generate the SSIS package to see you now have a Dest connection on your Controlflow.

One last thing before we move on. We need to define the ProtectionLevel of the package here as well:

*Remember – our Project and Package ProtectionLevels must match. Next lets add a Script Task where we will Truncate our Destination then we will add our Source and Dest Dataflow to wrap up this Introduction to Biml. 3.) Packages > Tasks, Variables, and Parameters

*Remember – our Project and Package ProtectionLevels must match.

Next lets add a Script Task where we will Truncate our Destination then we will add our Source and Dest Dataflow to wrap up this Introduction to Biml.

3.) Packages > Tasks, Variables, and Parameters

To Create a Variable: <Variables>      <Variable Name="VariableName"DataType="DataTypeHere">TheValue</Variable>  </Variables> To Create Tasks: <Tasks>   <ExecuteSQL Name = "" ConnectionName="" ResultSet="None">      <VariableInput VariableName="User.Variable"></VariableInput>   </ExecuteSQL>                        What it looks like in SSIS:

To Create a Variable:

<Variables>

     <Variable Name="VariableName"DataType="DataTypeHere">TheValue</Variable>

 </Variables>

To Create Tasks:

<Tasks>

  <ExecuteSQL Name = "" ConnectionName="" ResultSet="None">

     <VariableInput VariableName="User.Variable"></VariableInput>

  </ExecuteSQL>

                       What it looks like in SSIS:

For our Dataflow, it will only create an Empty Dataflow Task, next we will configure it. To Configure DataFlow: (Ours will include a Source, RowCount, and Destination) *Go ahead and change your ConstraintMode to “Linear” as we want to Truncate first then Load. *Add another variable that will hold our RowCount Data

For our Dataflow, it will only create an Empty Dataflow Task, next we will configure it.

To Configure DataFlow: (Ours will include a Source, RowCount, and Destination)

*Go ahead and change your ConstraintMode to “Linear” as we want to Truncate first then Load.

*Add another variable that will hold our RowCount Data

DataFlow Source Input Options: <VariableInput VariableName=”” <ExternalTableInput Table=”” <TableInput TableName=”” <TableFromVariableInput VariableName=”” OleDbSource Provider a name, which connection to use, and your Source Query: <OleDbSource Name=”PersonSourceTable” ConnectionName=”Source”> <DirectInput>  SELECTCAST(FirstName AS VARCHAR(50)) AS FirstName , CAST(LastName AS VARCHAR(50)) AS LastName , LEFT(CAST(MiddleName AS VARCHAR(1)), 1) AS MiddleInitial FROM    Person.Person; </DirectInput>     </OleDbSource> RowCount Provider a name and a Variable for your RowCount: <RowCount Name=”PersonExtractCount”VariableName=”User.PersonExtractRowCount”></RowCount> OleDbDestination   Provide a Name, a Connection, and point to a table using ExternalTableOutput <OleDbDestination Name=”Dest_Person” ConnectionName=”Dest”> < ExternalTableOutput Table=”dbo.dest_person”></ExternalTableOutput> < /OleDbDestination>     We have three segments in this Dataflow(OleDbSource, RowCount, and OleDbDestination). Which resembles the following in SSIS:

DataFlow Source Input Options:

<VariableInput VariableName=””

<ExternalTableInput Table=””

<TableInput TableName=””

<TableFromVariableInput VariableName=””

OleDbSource

Provider a name, which connection to use, and your Source Query:

<OleDbSource Name=”PersonSourceTable” ConnectionName=”Source”>

<DirectInput>  SELECTCAST(FirstName AS VARCHAR(50)) AS FirstName ,
CAST(LastName AS VARCHAR(50)) AS LastName ,
LEFT(CAST(MiddleName AS VARCHAR(1)), 1) AS MiddleInitial
FROM    Person.Person;
</DirectInput> 

   </OleDbSource>

RowCount

Provider a name and a Variable for your RowCount:

<RowCount Name=”PersonExtractCount”VariableName=”User.PersonExtractRowCount”></RowCount>

OleDbDestination

 

Provide a Name, a Connection, and point to a table using ExternalTableOutput

<OleDbDestination Name=”Dest_Person” ConnectionName=”Dest”>
< ExternalTableOutput Table=”dbo.dest_person”></ExternalTableOutput>
< /OleDbDestination>

 

 

We have three segments in this Dataflow(OleDbSource, RowCount, and OleDbDestination). Which resembles the following in SSIS:

Re-Generate your SSIS package and go ahead and Execute.

Re-Generate your SSIS package and go ahead and Execute.

  Final Biml File: Soon we will be discussing Automation using Biml, but even here you can see how reusable Biml can be. If we need to create another Truncate and Load SSIS package in the future, all we would need to do is change the Sources and Destinations(Connections/Tables).

 

Final Biml File:

Soon we will be discussing Automation using Biml, but even here you can see how reusable Biml can be. If we need to create another Truncate and Load SSIS package in the future, all we would need to do is change the Sources and Destinations(Connections/Tables).