prathmanu
Reputation: 93
BIML Visual studio validation error
<#@ import namespace="System.Data" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<# var connection = SchemaManager.CreateConnectionNode("SchemaProvider", "Data Source=servername\\SQLEXPRESS;Initial Catalog=SSISIncrementalLoad_Source;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>
<# var tables = connection.GenerateTableNodes(); #>
<# var sConn = "Provider=SQLNCLI11.1;Server=servername\\SQLEXPRESS;Initial Catalog=MyDwh_meta;Integrated Security=SSPI;"; #>
<# var sSQL = "SELECT * FROM SsisPackages"; #>
<# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<# foreach (DataRow row in tblPackages.Rows){ #>
<Connections>
<AdoNetConnection Name="Source"
ConnectionString="Data Source=<#=row["Source"]#>;Initial Catalog=<#=row["DbName"]#>;Integrated Security=True;"
Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
CreateInProject="true"
/>
<AdoNetConnection Name="Destination"
ConnectionString="Data Source=<#=row["Destination"]#>;Initial Catalog=<#=row["DbName"]#>;Integrated Security=True;"
Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
CreateInProject="true"
/>
<AdoNetConnection Name="Destination_Updates"
ConnectionString="Data Source=<#=row["Destination"]#>;Initial Catalog=<#=row["DbName"]#>_Update;Integrated Security=True;"
Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
CreateInProject="true"
/>
<AdoNetConnection Name="Destination_Deletes"
ConnectionString="Data Source=<#=row["Destination"]#>;Initial Catalog=<#=row["DbName"]#>_Delete;Integrated Security=True;"
Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
CreateInProject="true"
/>
</Connections>
<Packages>
<Package Name="<#=row["PackageName"]#>" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey">
<Variables>
<Variable Name="CDC_State" DataType="String"></Variable>
</Variables>
<Tasks>
<CustomTask Name="CDC Get processing range"
CreationName="Attunity.SqlServer.CDCControlTask.CdcControlTask, Attunity.SqlServer.CDCControlTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c">
<ObjectData>
<![CDATA[<CDCControlTask
Connection="Source"
TaskOperation="GetProcessingRange"
OperationParameter=""
StateConnection="Destination"
StateVariable="User::CDC_State"
AutomaticStatePersistence="True"
StateName="CDC_State"
StateTable="[dbo].[cdc_states]"
CommandTimeout="30"
ChangeDetectionRetryInterval="10"
ChangeDetectionTimeout="60" />]]>
</ObjectData>
</CustomTask>
<# foreach (var table in tables) { #>
<Container Name="SEQ DepartmentGroup_<#=table.Name#>" ConstraintMode="Linear">
<Tasks>
<Dataflow Name="DFT Incremental load_<#=table.Name#>">
<Transformations>
<CustomComponent Name="CDCSource"
ComponentClassId="{874F7595-FB5F-40FF-96AF-FBFF8250E3EF}"
ComponentTypeName="Attunity.SqlServer.CDCSrc.CdcSourceComponent, Attunity.SqlServer.CDCSrc, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
ContactInfo="Attunity Ltd.; All Rights Reserved; http://www.attunity.com;"
UsesDispositions="true"
Version="2"
ValidateExternalMetadata="false">
<Annotations>
<Annotation AnnotationType="Description">Specifies the content of the metadata columns and the rows returned. Modes starting with ‘All’ return all changes and modes starting with ‘Net’ return net changes.</Annotation>
</Annotations>
<Connections>
<Connection Name="Connection" ConnectionName="Source" />
</Connections>
<CustomProperties>
<CustomProperty Name="StateVariable"
DataType="String"
TypeConverter="Attunity.SqlServer.CDCSrc.PackageVariablesTypeConverter, Attunity.SqlServer.CDCSrc, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
SupportsExpression="true"
Description="An SSIS string package variable to store the CDC state of the current CDC state."
>User::CDC_State</CustomProperty>
<CustomProperty Name="CDCProcessingMode"
DataType="Int32"
TypeConverter="Attunity.SqlServer.CDCSrc.CdcProcessingMode, Attunity.SqlServer.CDCSrc, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
SupportsExpression="true"
Description="Specifies the content of the metadata columns and the rows returned. Modes starting with ‘All’ return all changes and modes starting with ‘Net’ return net changes."
>2</CustomProperty>
<CustomProperty Name="CaptureInstance"
DataType="String"
SupportsExpression="true"
Description="The name of the CDC capture instance. By default the capture instance name is in the form of "<owner>_<table>". The actual table that stores the changes is the CDC table named "cdc"."<capture-instance>_CT""
><![CDATA[dbo_<#=table.Name#>]]></CustomProperty>
<CustomProperty Name="CommandTimeout"
DataType="Int32"
SupportsExpression="true"
Description="The number of seconds before a command times out. A value of 0 indicates an infinite time out."
>30</CustomProperty>
<CustomProperty Name="ReprocessingIndication"
DataType="Boolean"
SupportsExpression="true"
Description="If true, a special output column called ‘__$reprocessing ’ is added. This output column is true for each data row during the initial processing range, or when the previous CDC run
was stopped without finishing. The default value is false (the __$reprocessing output column is not generated). This special output column allows the SSIS developer to handle consistency errors differently when working on the Initial Processing Range or the previous CDC run was stopped."
>false</CustomProperty>
</CustomProperties>
<OutputPaths>
<OutputPath Name="Output" >
<OutputColumns>
<OutputColumn Name="__$start_lsn" ExternalMetadataColumnName="__$start_lsn" DataType="Binary" Length="10" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent"/>
<OutputColumn Name="__$operation" ExternalMetadataColumnName="__$operation" DataType="Int32" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<OutputColumn Name="__$update_mask" ExternalMetadataColumnName="__$update_mask" DataType="Binary" Length="128" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent"/>
<# foreach (var column in table.Columns) { #>
<# if (column.DataType == System.Data.DbType.AnsiString) { #>
<OutputColumn Name="<#=column.Name#>" ExternalMetadataColumnName="<#=column.Name#>" DataType="String" Length="<#=column.Length#>" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<# } else { #>
<OutputColumn Name="<#=column.Name#>" ExternalMetadataColumnName="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<# } #>
<# } #>
</OutputColumns>
<ExternalColumns>
<ExternalColumn Name="__$start_lsn" DataType="Binary" Length="10" />
<ExternalColumn Name="__$operation" DataType="Int32" />
<ExternalColumn Name="__$update_mask" DataType="Binary" Length="128" />
<# foreach (var column in table.Columns) { #>
<# if (column.DataType == System.Data.DbType.AnsiString) { #>
<ExternalColumn Name="<#=column.Name#>" DataType="String" Length="<#=column.Length#>" />
<# } else { #>
<ExternalColumn Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" />
<# } #>
<# } #>
</ExternalColumns>
</OutputPath>
<OutputPath Name="ErrorOutput" IsErrorOutput="true">
<OutputColumns>
<OutputColumn Name="__$start_lsn" DataType="Binary" Length="10" />
<OutputColumn Name="__$operation" DataType="Int32" />
<OutputColumn Name="__$update_mask" DataType="Binary" Length="128" />
<# foreach (var column in table.Columns) { #>
<# if (column.DataType == System.Data.DbType.AnsiString) { #>
<OutputColumn Name="<#=column.Name#>" DataType="String" Length="<#=column.Length#>" />
<# } else { #>
<OutputColumn Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" />
<# } #>
<# } #>
</OutputColumns>
</OutputPath>
</OutputPaths>
</CustomComponent>
<CustomComponent Name="CDCSplitter"
ComponentClassId="{874F7595-FB5F-40FF-96AF-FBFF8250E3EF}"
ComponentTypeName="Attunity.SqlServer.CDCSplit.CdcSplitterComponent, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
ContactInfo="Attunity Ltd.; All Rights Reserved; http://www.attunity.com;"
UsesDispositions="true"
Version="1"
ValidateExternalMetadata="false">
<Annotations>
<Annotation AnnotationType="Description">Directs a stream of net change records into different outputs based on the type of the change (Insert, Delete and Update). This allows specific handling for different types of change records.</Annotation>
</Annotations>
<InputPaths>
<InputPath Identifier="Input" OutputPathName="CDCSource.Output" >
<InputColumns>
<InputColumn SourceColumn="__$start_lsn" />
<InputColumn SourceColumn="__$operation" />
<InputColumn SourceColumn="__$update_mask" />
<# foreach (var column in table.Columns) { #>
<InputColumn SourceColumn="<#=column.Name#>" />
<# } #>
</InputColumns>
</InputPath>
</InputPaths>
<OutputPaths>
<OutputPath Name="InsertOutput" SynchronousInput="Input" ExclusionGroup="1">
<Annotations>
<Annotation AnnotationType="Description">Output type - Insert.</Annotation>
</Annotations>
<CustomProperties>
<CustomProperty Name="OutputType" DataType="Int32"
TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
>0</CustomProperty>
</CustomProperties>
<OutputColumns>
</OutputColumns>
<ExternalColumns />
</OutputPath>
<OutputPath Name="UpdateOutput" SynchronousInput="Input" ExclusionGroup="1">
<Annotations>
<Annotation AnnotationType="Description">Output type - Update.</Annotation>
</Annotations>
<CustomProperties>
<CustomProperty Name="OutputType" DataType="Int32"
TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
>1</CustomProperty>
</CustomProperties>
<OutputColumns>
</OutputColumns>
</OutputPath>
<OutputPath Name="DeleteOutput" SynchronousInput="Input" ExclusionGroup="1">
<Annotations>
<Annotation AnnotationType="Description">Output type - Delete.</Annotation>
</Annotations>
<CustomProperties>
<CustomProperty Name="OutputType" DataType="Int32"
TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
>2</CustomProperty>
</CustomProperties>
<OutputColumns>
</OutputColumns>
</OutputPath>
<OutputPath Name="ErrorOutput" IsErrorOutput="true" SynchronousInput="Input" ExclusionGroup="1">
<Annotations>
<Annotation AnnotationType="Description">Output type - Error.</Annotation>
</Annotations>
<CustomProperties>
<CustomProperty Name="OutputType" DataType="Int32"
TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"
>3</CustomProperty>
</CustomProperties>
<OutputColumns>
</OutputColumns>
<ExternalColumns />
</OutputPath>
</OutputPaths>
</CustomComponent>
<AdoNetDestination Name="ADO_DST Destination" ConnectionName="Destination">
<InputPath OutputPathName="CDCSplitter.InsertOutput" />
<ExternalTableOutput Table="[dbo].<#=table.Name#>" />
</AdoNetDestination>
<AdoNetDestination Name="ADO_DST Staging UPDATES" ConnectionName="Destination_Updates">
<InputPath OutputPathName="CDCSplitter.UpdateOutput" />
<ExternalTableOutput Table="[dbo].[<#=table.Name#>]" />
</AdoNetDestination>
<AdoNetDestination Name="ADO_DST Staging DELETES" ConnectionName="Destination_Deletes">
<InputPath OutputPathName="CDCSplitter.DeleteOutput" />
<ExternalTableOutput Table="[dbo].[<#=table.Name#>]" />
</AdoNetDestination>
</Transformations>
</Dataflow>
<ExecuteSQL BypassPrepare="false" Name="SQL Handle updates in batch_<#=table.Name#>" ConnectionName="Destination_Updates" >
<# string upd ="Update Dest Set ";
foreach (var colex in table.Columns.Where(column => !table.Keys[0].Columns.Select(keyColumn => keyColumn.Column).Contains(column))) {
upd = upd + "Dest." + colex + " = Upd." + colex + ",";
}
var updc = upd.Substring(0,upd.Length-1) + " From " + table.SchemaQualifiedName + " Dest Join [" + row["DbNameUpdate"] + "].[" + table.Name + "] Upd On Upd." + table.Keys[0].Columns[0].Column + " = Dest." + table.Keys[0].Columns[0].Column;#>
<DirectInput>
<#=updc#>
</DirectInput>
</ExecuteSQL>
<ExecuteSQL BypassPrepare="false" Name="SQL Handle deletes in batch_<#=table.Name#>" ConnectionName="Destination_Deletes">
<# string del="DELETE FROM ["+ table.Name +"] WHERE ["+table.Keys[0].Columns[0].Column +"] IN ( SELECT ["+ table.Keys[0].Columns[0].Column +"] FROM [" + row["DbNameDelete"] + "].["+ table.Name +"])"; #>
<DirectInput>
<#=del#>
</DirectInput>
</ExecuteSQL>
<ExecuteSQL BypassPrepare="false" Name="SQL Drop staging tables Update_<#=table.Name#>" ConnectionName="Destination_Updates">
<# string Drop_upd="TRUNCATE TABLE ["+ table.Name +"]"; #>
<DirectInput>
<#=Drop_upd#>
</DirectInput>
</ExecuteSQL>
<ExecuteSQL BypassPrepare="false" Name="SQL Drop staging tables Delete_<#=table.Name#>" ConnectionName="Destination_Deletes">
<# string Drop_del="TRUNCATE TABLE ["+ table.Name +"]"; #>
<DirectInput>
<#=Drop_del#>
</DirectInput>
</ExecuteSQL>
<!--Close Container-->
</Tasks>
</Container>
<# } #>
<CustomTask Name="CDC Mark processed range"
CreationName="Attunity.SqlServer.CDCControlTask.CdcControlTask, Attunity.SqlServer.CDCControlTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c">
<ObjectData>
<![CDATA[<CDCControlTask
Connection="Source"
TaskOperation="MarkProcessedRange"
OperationParameter=""
StateConnection="Destination"
StateVariable="User::CDC_State"
AutomaticStatePersistence="True"
StateName="CDC_State"
StateTable="[dbo].[cdc_states]"
CommandTimeout="30"
ChangeDetectionRetryInterval="10"
ChangeDetectionTimeout="60" />]]>
</ObjectData>
</CustomTask>
</Tasks>
</Package>
</Packages>
<# } #>
</Biml>I am working on a SSIS project using BIML. The code was working perfectly but immediatley Biml validator showing error
Validating BIML Error 0 : BimlScript code produced an exception: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.ThrowHelper.ThrowArgumentOutOfRangeException in :line 0
at System.Collections.Generic.List1.get_Item in :line 0
at Varigence.Utility.TextTemplating.GeneratedTextTransformation5b7174f5b7ed4045af6ad5d04aa325a2+<>c__DisplayClass3.<TransformText>b__0 in CDC_Final.biml:line 255
at System.Linq.Enumerable+WhereEnumerableIterator1.MoveNext in :line 0
at BimlScriptCode in CDC_Final.biml:line 255. Exception type: ArgumentOutOfRangeException
Parse. There were errors during compilation. See compiler output for more information.
Please help to resolve this error
Upvotes: 0
Views: 2443
Answers (2)
Sean
Reputation: 1474
It looks like the problem is that not all the tables in your database have keys defined on them. So when the BIML compiler gets here:
foreach (var colex in
table.Columns.Where(column => !table.Keys[0].Columns.
Select(keyColumn => keyColumn.Column).Contains(column))) {
it fails because table.Keys is empty. I have put an if statement around both ExecuteSQL blocks, i.e. the destination update and delete and the error goes away. Not sure what the logic should be in the case where the table doesn't have a key, or perhaps the problem is that all tables should have keys.
Upvotes: 2
billinkc
Reputation: 61249
Somewhere in either your static biml or your bimlscript, you've gotten off with your tag count. It could be BIML tags, like </Task> or it might be C# tags like }
What you're seeing is basically the Biml compiler breaking as it parses the content.
Upvotes: 1
Related Questions
- How to fix a Failed Task for a Biml-Built SSIS Script Task: No such interface supported
- Visual Studio 2019 "Open With..." BimlExpress Files Error "The Parameter is incorrect"
- BIML generated SSIS package won't load with error 0x80070057 "The parameter is incorrect." CPackage::LoadFromXML fails
- Intellisense not working while writing BIMLScript in SQL Server Business Intelligent Studio 2008(SSIS)
- Cant save BIML file in Visual Studio
- unable to generate BIML for script component
- BIML enable SSIS Package Configuration
- BIML Annotation Tag not matching
- BIML never generate Package ProtectionLevel properly
- Business Intelligence Development Studio stuck Validating a task in a dtsx file