Introduction

The BimlSnap Application is Windows based application and an extended version of BimlSnap Web Application (free version) that uses a local database.

​

BimlSnap Application allows you to create SSIS Projects, Connections, Parameters (manual or auto-created), several types of Package Patterns by simply using the forms without programming knowledge, and with one button click to create the .biml and .params (XML) files.

​

What the desktop application offers you is more connection types (ADO.Net, Flat Files besides Data and SMTP), more package patterns (Execute Process, Execute Script besides Data Flow, Data Flow Expression, Execute SQL, Execute SQL Expression), usage of BimlSnap ‘accelerators’ such as the MERGE statement generator within the Execute SQL and Execute Process package patterns.

​

What also makes this application different from the web application is the support for populating a Snowflake Data Warehouse, because the SnowSQL statements are built within the applications code generators.

In addition to set up everything and use the application, please contact us via info@bitracks.com or fill in the contact form under the Contact Us section and we will be happy to help you.

​

Projects
Creating a Projects lets you organize your connections, parameters, packages and environments within the one unit. That means that the data that you create under the one project name, when being build will be contained in .biml and .params (XML) file which are later important for generating SSIS packages. Also, you will want to give your project a name that is the same as the associated Visual Studio ‘Project Name’.

​

User has the option to choose the Template Group and Package Protection Level. Note that based on the ‘Template Group’ selection, certain ‘Connection and Parameter’ resources may automatically be added to your BimlSnap project.

 

Template Group

There are 3 ‘Template Group’ options:

• Standard

• No Alerts

• No Framework

 

The ‘Standard’ template provides the following Framework features:

• Project restart ability

• Package row counts

• Package runtimes

• Email alerts

• Package error logging

​

The ‘No Alerts’ option Provides all the ‘Standard’ template features minus ‘Email alerts’.

​

The ‘No Framework’ option is a minimalist template and can be used when none of the above mentioned features are not required.

 

These Template options can be changed at any time.

​

Package Protection Level

There are 2 ‘Package Protection Level’ options:

• Encrypt Sensitive with User Key

• Don’t Save Sensitive
   

The first option ‘Encrypt Sensitive with User Key’ is used to encrypt sensitive information based on the credentials of the user that created (or generated) the package. This is also the default protection level that is used when creating a new project in Visual Studio (SSDT).

​

The option ‘Don’t Save Sensitive’ can be used to omit sensitive data. This ‘package’ setting needs to correspond to the equivalent ‘project’ level setting in Visual Studio.

​

Connections

​

Connections are almost the same as they are in Visual Studio, but the benefit of creating those through BimlSnap application auto-generates other items.

​

In order to properly set up a project, at least one connection needs to be created. Every created connection within the project will be driven by parameters for that specific connection.

There are four types of connections:

• Data

• SMTP

• .Net

• Flat File

 

Depends on which type of connection you need.

​

Data Connections

Through the application you can:

• Add a new Data Connection

• Edit an existing one

• Delete an existing one

​

To create a new Data Connection based on the Windows Integrated security, you will need to provide the:

​

• Connection Name – it is recommend to make the Connection Name the same as the Database Name. That actual connection will later be driven by parameters (which are automatically created) to configure the connection with a run-time expression

• Server Name – actual server name for the specific connection

• Database Name – existing database name for specified server

• Custom Connect String – expression that uses the SQL Standard security or to have bigger control over the connect string

SMTP Connections

Through the application you can:

• Add a new connection SMTP Connection

• Edit an existing one

• Delete an existing one

 

To create a new SMTP Connection you will need to provide the:

• Connection Name

• SMTP Server Name – actual SMTP server name

​

Ado.Net Connections

Ado.Net Connection enables the package to access the data source by using .Net provider. This type of connection is typically used to access the data source such as Microsoft SQL Server, and also data sources exposed through OleDb connections.
 

Through bimlSnap you can:

• Add a new connection Ado.Net Connection

• Edit an existing one

• Delete an existing one

​

To create a new Ado.Net Connection, you will need to provide:

• Connection Name

• Provider Name

• Connection String – string that represents the connection to your server

• Database Name – actual database on your server

• The last two columns are computed, so you won’t need to provide any data for those two.

​

Flat Files Connections

The Flat File Connections are used to access the flat files that are stored somewhere locally.

Through the application you can:

• Add a new connection Flat File Connection

• Edit an existing one

• Delete an existing one

​

To create a new Ado.Net Connection, you will need to provide:

• Connection Name

• File path – the physical path to the location where the file is stored

• File format – the format of the file that is stored

The last two columns are computed, so you won’t need to provide data to those.

​

Parameters

​

Based on the type of created Connection, Parameters will be automatically created with predefined values. Those automatically created Parameters can be edited and also there is an option to create a stand alone parameters.

​

Packages

Packages are the place where the data data movement and transformation happens.

BimlSnap Desktop application has predefined ‘patterns’:

• Data Flow

• Execute SQL

• Foreach Data Flow

• Foreach Execute SQL

• Execute Script

• Execute Process

 

If you take a look from a technical perspective, packages are a Biml root type. They contain one or more elements, depending on the package pattern selected.

​

The Execute SQL pattern is generally used for transformation after data is ‘piped’ to a central database server. That leads us to our acronym, this is a ‘T’ in ‘ELT’.

​

Data Flow Package Pattern

The ‘Data Flow’ Package Pattern is used for moving data between source and destination, The Data Flow task is essentially a pipeline to move data from a ‘source’ to a ‘destination’ connection. This can also be considered the ‘EL’ (Extract and Load) within the common ‘ELT’ acronym.

​

Bimlsnap allows you to sequence the order of execution by auto-configuring precedence constraints which control package execution. Multiple packages can be assigned the same ‘sequence number’, and in this case the generated packages will run in parallel.

​

Data Flow Expression Package Pattern

The ‘Data Flow Expression’ pattern is identical to the ‘Data Flow’ pattern with the single exception that the source query provided is based on an “SSIS expression”.

 

For each Data Flow Package Pattern

One of the handiest features in SQL Server Integration Services (SSIS) is the ability to implement looping logic within your control flow. By using the Foreach Loop container, you can create a looping structure that iterates through a collection of objects or data values and take actions specific to each of those members.

The Foreach Data Flow Package Pattern defines a repeating control flow in a SSIS package. This allows you to load multiple source tables into a single destination connection.

 

Execute SQL

The Execute SQL Package runs SQL statements or stored procedures. It can contain either a single SQL statement or multiple SQL statements that run sequentially.

​

The Execute SQL task is one of the handier components in SQL Server Integration Services (SSIS) because it lets you run Transact-SQL statements from within your control flow. When using an ‘ELT’ approach to data integration, the Execute SQL Package pattern will generally follow the Data Flows ‘EL’ (ie., Extract and Loading) to perform the ‘T’ (data Transformations).

​

This package pattern has 4 code generators​

1. Query Config – Fact Table Partition   Very large tables can be difficult to manage because of their size and the amount of time it takes to maintain. BimlSnap includes ‘code generators’ which can be used to ‘partition’ these large tables into manageable segments.Before using the table partitioning code generators, you must first manually create the table’s associated partition function and scheme. For more information on this task, see:https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes .In addition, for the BimlSnap code generators to work, the partitioning column must be defined as a ‘date’ datatype, and all indexes for the table need to be ‘partitioned aligned’.

2. Query Config – Dim Table Merge   The generated Merge statement conforms to the Kimball ‘Type 1’ and ‘Type 2’ dimension model. Use the SnapMart metadata application to specify the dimension type to be used for each attribute The MERGE statement is used when you want to apply changes that include inserts and updates of data based on the contents of the associated ‘staging’ table.In order to do that by using the Code generator ‘Query Config – Dim Table Merge’, you will need to provide the Metadata Database, that is database parameter for the specific OleDb Connection. After that you will need to provide the Merge Source (Staging Table) Schema, Table and Database Parameter and Merge Destination (Dimension Table) Schema, Table and Database Parameter, and the Added Dimension Column Naming Schema.The Added Dimension Column Naming Schema can be chosen from the dropdown as predefined ‘Standard’ or can be added by clicking ‘Open Added Column Naming Form’ button.

3. Query Config – Fact Table Merge   One of the benefits of the table partitioning is that you can speed up loading the data by using partition switching. Partition switching moves entire partition between tables almost instantly.To switch the partitions between tables, you need to follow the following requirements:

   • The source and destination partitions must have identical columns, indexes and use the same partition column, scheme and function

   • The source and destination partitions must exist on the same filegroup

   • The ‘switch-to’ destination partition must be empty
     In order to generate the Source Expression with the ‘Query Config – Fact Table Switch’ Code Generator, click the ‘Query Config – Fact Table Switch’ button. Provide the data for Partitioned Source (Switch-In) Table, select or type in the Table Schema and Table. Select or type in the Table Schema and Table for Partitioned Destination (Switch-To) Table and repeat the same for the Partitioned Removal (Switch-Out) Table.Then select one of the options for the Switch Option ‘Start’ Date. Note that when you choose the ‘use Project Level Parameter’ option, you will need to select a Project Parameter. Otherwise, it’s not needed.

4. Query Config – Fact Table Switch   One of the benefits of the table partitioning is that you can speed up loading the data by using partition switching. Partition switching moves entire partition between tables almost instantly.To switch the partitions between tables, you need to follow the following requirements:

   • The source and destination partitions must have identical columns, indexes and use the same partition column, scheme and function

   • The source and destination partitions must exist on the same filegroup

   • The ‘switch-to’ destination partition must be empty