Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vipin_jha123
Starting Member
31 Posts |
 Posted - 2014-12-23 : 23:52:55
|
| Hi,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 scenarioBelow is my BIML Script.<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 |
|
|
|
|
|
OLEDB SOURCE IN BIML