Ошибка проверки визуальной студии BIML
<#@ 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>Я работаю над проектом SSIS, используя BIML. Код работал отлично, но сразу же Biml валидатор показывает ошибку
Проверка BIML Ошибка 0: Код BimlScript выдал исключение: Индекс был вне диапазона. Должен быть неотрицательным и меньшим, чем размер коллекции. Имя параметра: индекс в System.ThrowHelper.ThrowArgumentOutOfRangeException in: строка 0 в System.Collections.Generic.List 1.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+WhereEnumerableIterator 1.MoveNext in: строка 0 в BimlScriptCode в CDC_Final.biml: строка 255. Тип исключения: ArgumentOutOfRangeException
Parse. Были ошибки во время компиляции. Смотрите вывод компилятора для получения дополнительной информации.
Пожалуйста, помогите решить эту ошибку
2 ответа
Похоже, проблема в том, что не все таблицы в вашей базе данных имеют определенные ключи. Итак, когда BIML-компилятор попадает сюда:
foreach (var colex in
table.Columns.Where(column => !table.Keys[0].Columns.
Select(keyColumn => keyColumn.Column).Contains(column))) {
это терпит неудачу, потому что table.Keys пустой. Я поместил заявление if вокруг обоих ExecuteSQL блокирует, то есть назначение обновляет и удаляет и ошибка исчезает. Не уверен, что логика должна быть в случае, когда таблица не имеет ключа, или, возможно, проблема в том, что все таблицы должны иметь ключи.
Где-то в вашем статическом biml или в вашем bimlscript у вас получилось подсчитать количество тегов. Это могут быть теги BIML, например </Task> или это могут быть теги C#, такие как }
То, что вы видите, это в основном разрыв компилятора Biml, когда он анализирует содержимое.