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(C#).

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

<!-- 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;" />

</Connections>

<!-- Name Of the the Package-->

<Packages>

<Package Name="LoadArchive Using BIML" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" >

<Variables>

<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>

<Variable Name="v_Src_FileName" DataType="String" >FBAPP</Variable>

                                               

<!-- Load Data Truncate Staging Sequence Container--> 

<Container Name="Load Data Truncate Staging" ConstraintMode="Parallel">

<Tasks>

<Dataflow Name="Archive Data" DelayValidation="true" >

<Transformations>

<OleDbSource Name="Source" ConnectionName="DataStaging" ValidateExternalMetadata="false">

<TableFromVariableInput VariableName="User.V_Archivequery"/>

</OleDbSource>            

                    

</Transformations>

</Dataflow>

</Tasks>

</Container>

</Tasks>

</Package>

</Packages>

</Biml>

Regards,

Vipin jha

Hi Vipin,

very few people would respond here, why don't you post to the BIML dedicated forum: http://varigence.com/Forums?forumName=Biml&threadID=0

I have the exact same issue with Vipin, and constantly looking for solution for this in Biml. In SSIS, it is fine to use variable as data source. But in Biml, if the variable value has been assigned in previous task, it looks like Biml builder doesn't recognize the new value of the variable. 

How to get dynamic data source in Biml?