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​
-
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’.
-
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.
-
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.
-
-
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
-
Execute Script
The Execute Script Package Pattern is mainly used for executing Scripts that are predefined.
What this package patterns makes important is the ability to execute the predefined script that is SnowSQL based.
​
Execute Process
The Execute Process Package Pattern lets the user to use his own executable application to run it. In that case user can run an application for importing data to the database or to move files from one directory to another.
​
Sequence Number
All packages added to the project will have sequence number zero at first, but it can be changed. This number is used to determine package execution order. If two packages have same sequence number it will run in parallel, otherwise the packages will run sequentially (from lower number to higher).
Environments
Environments are used to generate packages for a specific environment. After the selected project is build, the .biml and .params for specific environment can be build.
​
They can be manually added. First you will need to create a Environment and then you can create an Environment Parameter for the specific environment. That parameter that can be assigned to specific environment is the key part, because it defines the connection to specific environment.
​
Build
If you build the selected project, the output will be .biml and .params (XML) files. Later, those generated files are used for generating SSIS packages. Every project is build by the values that the user has defined for the Template Group and Package Protection Level. The Template Group is the same value as it was while the Project was created (Standard), and the Package Protection Level has predefined selected value (Encrypt Sensitive With User Key). After the build is finished, any error can be reviewed and the progress can be tracked within the ‘Build Progress’ box. Those generated files can be downloaded or copied directly in the Visual Studio to generate the SSIS Packages.
Utilities
The following options are shown under utilities:
Select Statement Generator,
-
Flat File Header Utility
-
Flat File Column Alias
-
Flat File Formats
-
Export Data
-
Import Data
​
The ‘Select Statement Generator’ lets you create a Select Statement by choosing proper data for Source Table and Destination Table, and by clicking the ‘Generate’ button the select statement will be generated.
​
The ‘Flat File Header Utility’ generates the CREATE SQL statement based on the Original Headers that you typed in using specified delimiter. By clicking the arrow button it checks if there are any the same Original Headers, then adds a unique character and and by clicking the second arrow the CREATE SQL statement is generated.
The Flat File Column Alias is used in code generators such as Query Config – Dim Table Merge, to customize column names used in MERGE destination table.
​
The ‘Export Data’ functionality lets you to export the needed tables. There are options to export all tables, to include only the needed ones and to exclude the not needed ones. Clicking on the ‘Export’ button, the .json file will be saved on the default location on your PC.
​
VS Deploy
The VS Deploy application helps a user of bimlSnap to easily copy files to the folder where the VS Project for generating an SSIS Package is.
​
This application provides two types of connections to the database:
​
-
Windows Authentication
-
SQL Server Authentication
After the connection is succeeded, on the Copy tab, user can choose the Environment Name and Project Name, then specify the Visual Studio folder path by clicking the ‘Choose Folder’.
​
After that, by clicking the ‘Copy Files’ button, files will be copied to the selected path.
​
On the ‘Recent’ tab, previously copied files will appear and you can always copy them again. You will only need to check the files that you want to copy and they will be copied to the specified location.