BIML Script File

Dec 18, 2014 at 8:27 AM
Hi ,

I have created simple BIML Script file which generating a SSIS Package.
now issue is that my new SSIS package getting input from Master package , on the basis of that my new SSIS Package will be function.
Now I don't know how to schedule the BIML Script file.
My package flow will be like
Master Package >>Biml Script >>New SSIS Package .

please suggest me the best solution.

Thanks & Regards,
Vipin jha
Dec 18, 2014 at 10:05 AM
You would not normally schedule a BIML file, normally you would use the BIML file to generate a set of one or more SSIS packages and then you would use SQL Agent to run those packages on a schedule.

If you need to generate the packages on a schedule then you will need to look at purchasing the Mist product from varigence as it includes a command line tool called Hadron which you could schedule to compile your BIML Script. The BIML compiler included for free inside BIDS Helper does not have the ability to be scheduled
Dec 22, 2014 at 5:12 AM
Hi ,

I have some question in BIML, but I am unable to create any blog for the same. My issue is stated below.

I need to schedule BIML script to generate SSIS package. How can we do the same.

Thanks in advance,
Dec 22, 2014 at 8:50 PM
As I answered above - you need to purchase Mist from There is no other way to schedule the execution of a BIML Script.

BIDS Helper is a Visual Studio add-in and there is no way to schedule the execution of any of it's features. They are all designed to be triggered by a user.
Marked as answer by furmangg on 3/17/2015 at 11:43 AM
Dec 23, 2014 at 9:35 AM
Hi ,
I have created 1 sample BIML Script.
I want to take source query from master table using variable.
I am getting below error when i am using vaiable for same

below is the container code

Error:-Node Source:Coluld not Execute query on connection

<Container Name="Load Data Truncate Staging" ConstraintMode="Parallel">
<Dataflow Name="Archive Data" DelayValidation="true" >
<OleDbSource Name="Source" ConnectionName="DataStaging" ValidateExternalMetadata="false">
<TableFromVariableInput VariableName="User.__V_Archivequery__"/>

<OleDbDestination Name="Archive" ConnectionName="Archive" ValidateExternalMetadata="false">
          <TableFromVariableOutput VariableName="User.V_Archive_tablename"/>

Vipin jha
Dec 24, 2014 at 3:35 AM
Hi all, I am trying to automate a data flow with BIML. I am using an expression to build my SQL dynamically based on input parameter. Sparing details of the use case, I need this flexibility in my project. I am having 1 master table which consists of file name and source query.
I want to use SQL Command from Variable Data access mode in OLEDB Source.
On the basis of input file source query need to change automatically .However; my query is not being evaluated when the package is generated. The query will populate after package generation, when I open the source and set access mode SQL Command, but I cannot seem to get this configured automatically as desired. This is preventing me from doing transformations in the script.
Please help me to achieve the above scenario
Below is my BIML Script.
<Biml xmlns="">
<!-- Database Connection manager--> <Connections>
<Connection Name="Archive" ConnectionString="Data Source=RLDEVOLP03.DEVELOPMENT.LOCAL;Initial Catalog=Archive;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
<Connection Name="DataStaging" ConnectionString="Data Source=RLDEVOLP03.DEVELOPMENT.LOCAL;Initial Catalog=DataStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
<!-- Name Of the the Package--> <Packages>
<Package Name="LoadArchive Using BIML" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" >

<Variable Name="V_Archive_tablename" DataType="String" ></Variable>
<Variable Name="V_Archivequery" DataType="String" EvaluateAsExpression="true">SELECT a.*, b.BBxKey as Archive_BBxKey, b.RowChecksum as Archive_RowChecksum FROM dbo.ImportBBxFbapp a LEFT OUTER JOIN Archive.dbo.ArchiveBBxFbapp b ON a.Col001 = b.BBxKey Where (b.LatestVersion = 1 OR b.LatestVersion IS NULL)
<Variable Name="v_Src_FileName" DataType="String" >FBAPP</Variable>
<!-- Load Data Truncate Staging Sequence Container--> <Container Name="Load Data Truncate Staging" ConstraintMode="Parallel">
<Dataflow Name="Archive Data" DelayValidation="true" >
<OleDbSource Name="Source" ConnectionName="DataStaging" ValidateExternalMetadata="false">
<TableFromVariableInput VariableName="User.V_Archivequery"/>


Vipin jha
Dec 24, 2014 at 7:46 PM
Hi Vipin jha,

Change TableFromVariableInput to VariableInput.

There are five options in the source:
DirectInput .. from
VariableInput ..variable with select .. from
TableInput ..earlier defined table in your biml script in tables\table node
ExternalTableInput ..dbo.table
TableFromVariableInput variable with dbo.table
Dec 26, 2014 at 4:49 AM
Hi Sir ,
I am very much new to BIML . Thank you very much for your support

Issue 1:-As you know I am having 100 of different table to load using single SSIS Package, as you suggested I am using BIML Script for same.
Below is the derived column where expression of BBXKey is different as per the different table,
Here I want that value of expression should change on fly using variable, I will store the value of expression in master table.
My BIML Script:-

<DerivedColumns Name="Add Time_In and BBx_Key Fields">
<Column Name="TimeIn" DataType="DateTime">getdate()</Column>
<Column Name="BBxKey" DataType="String" Length="50">@V_Derived_Column_Query</Column>
<Column Name="LatestVersion" DataType="Int32">1</Column>

Please requesting you to help me in achieve the below scenario.

Issue 2-I know to configure Conditional split and Row count transformation in BIML Script, but I don’t know how to link the specific output to corresponding row count transformation.
My BIML Script:
<ConditionalSplit Name="Split_New_and_Updated_Records">

<OutputPath Name="New_Records"> <Expression>ISNULL(Archive_BBxKey)</Expression>

<OutputPath Name="Updated_Records">
<Expression>BBxKey == Archive_BBxKey && RowChecksum != Archive_RowChecksum</Expression>

<OutputPath Name="Unchanged_Records">
<Expression>BBxKey == Archive_BBxKey && RowChecksum == Archive_RowChecksum</Expression>


<RowCount Name="Count New Records" VariableName="User.newRecords"/>

Please requesting you to help me in achieve the below scenario.

Issue 3-Below is the Code of script component which is used to create the Rowchecksum column for every table,
As you can see that columns are hard coded in below script.
Now I have to convert below code into BIML Script, instead of hard coded I need to do it using expression or variable, it should take the table name from variable .
Col001,col002 are the column
Please requesting you to help me in achieve the below scenario.

Imports System
Imports System.Data
Imports System.Math
Imports System.Security.Cryptography
Imports System.Text
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent

Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008)

    Dim hash As Byte() = md5.ComputeHash(columnContents)
    Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)

    Row.RowChecksum = hashString
End Sub
End Class

Thanks & Regards,
Vipin jha