I'm making a IS task. I have a flatfile column named SCB_ActualMIN with a data type string [DT_STR]. I also have a script component to convert the SCB_ActualMIN column to numeric data type. I have this code in my script component
我在做一个IS任务。我有一个名为SCB_ActualMIN的平面文件列，它具有数据类型字符串[DT_STR]。我还有一个脚本组件，可以将SCB_ActualMIN列转换为数字数据类型。我的脚本组件中有这段代码
If Not Row.SCBActualDTime_IsNull AndAlso
Not String.IsNullOrEmpty(Row.SCBActualDTime.Trim) Then
Dim dtDate As Date
If DateTime.TryParse(Row.SCBActualDTime.Trim, dtDate) Then
Row.OutPutColumn = dtDate
Else
'If column cannot be parsed
Row.OutPutColumn_IsNull = True
End If
Else
Row.OutPutColumn_IsNull = True
End If
'''''SCBActualDTime
If Not Row.SCBActualMIN_IsNull AndAlso
Not String.IsNullOrEmpty(Row.SCBActualMIN.Trim) Then
Dim MIN As Integer
If Int32.TryParse(Row.SCBActualMIN.Trim, MIN) Then
Row.OutPut2Column = MIN
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
Else
Row.OutPut2Column_IsNull = True
End If
As you can see the conversion of string to datetime data type is successful. While using the same code for string to integer. The Int32.TryParse(Row.SCBActualMIN.Trim, MIN) always return false even I have the value 09764377211 in the line code. Also is there other way for me to avoid the repetition of code.
可以看到，将字符串转换为datetime数据类型是成功的。当使用相同的代码为字符串到整数。Int32.TryParse(Row.SCBActualMIN。即使我在行代码中有09764377211的值，也总是返回false。还有其他方法可以让我避免代码的重复。
2 个解决方案
#1
2
9764377211 is bigger than 2147483647 which is the maximum value that can be assigned to Int32
9764377211大于2147483647，这是可以分配给Int32的最大值
Try converting value to Int64 or Double instead of Int32
尝试将值转换为Int64或Double，而不是Int32
Dim MIN As Int64
If Int64.TryParse(Row.SCBActualMIN.Trim, MIN) Then
Row.OutPut2Column = MIN
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
OR
或
Dim MIN As Double
If Double.TryParse(Row.SCBActualMIN.Trim, MIN) Then
Row.OutPut2Column = MIN
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
You cannot avoid repetition because you have to check each column for null by using it is own _IsNull property. You can try minimizing code by creating functions.
您不能避免重复，因为您必须通过使用它的_IsNull属性来检查每个列是否为空。您可以尝试通过创建函数来最小化代码。
#2
2
First of all, like @Yahfoufi suggested the exception is thrown because the value "9764377211 is bigger than 2147483647 which is the maximum value that can be assigned to Int32..."
首先，@Yahfoufi建议抛出异常，因为“9764377211大于2147483647，这是可以分配给Int32的最大值…”
You can Read more in this MSDN article about Data Types and corresponding Maximum values.
您可以在这篇MSDN文章中阅读更多关于数据类型和相应的最大值的文章。
Type Storage size Range
Int32 4 bytes -2,147,483,648 to 2,147,483,647
Int64 8 bytes Approximately -9.2E+18 to 9.2E+18
Double 8 bytes Approximate range is -1.79E+308 to 1.79E+308 with accuracy of about 15 digits. Can represent numbers as small as 1E-323.
Decimal 12 bytes Approximate range is -7.9E+28 to 7.9E+28 with accuracy of 28 digits. Can represent numbers as small as 1E-28.
...
You can assign this value to Int64 or Decimal or Double Data Types
可以将此值赋给Int64或Decimal或Double数据类型
Optimizing your code
优化代码
In this case you cannot avoid the repetition of these parts of code because there are using independent properties that cannot be called dynamically:
在这种情况下，您无法避免重复代码的这些部分，因为使用了不能动态调用的独立属性:
-
Checking if Input Columns is Null or Empty
检查输入列是否为空。
If Not Row.SCBActualMIN_IsNull AndAlso Not String.IsNullOrEmpty(Row.SCBActualMIN.Trim) Then -
Assigning
Nullto the output column if value cannot be parsed or input isNullor Empty如果值无法解析或输入为空或空，则将Null分配给输出列
Row.OutPut2Column_IsNull = True
You can modify your code to minimize number of lines but i don't think it will improve the performance
您可以修改代码以减少行数，但我认为这不会提高性能
- For each Data Type declare one variable inside the RowProcessing Sub and make your code as the following:
- 对于每个数据类型，在RowProcessing Sub中声明一个变量，并使代码如下所示:
Assuming that these 2 columns are containing dates
假设这两列包含日期
Dim dtDate As Date
If Not Row.SCBActualDTime_IsNull AndAlso
Not String.IsNullOrEmpty(Row.SCBActualDTime.Trim) AndAlso
DateTime.TryParse(Row.SCBActualDTime.Trim, dtDate)Then
Row.OutPutColumn = dtDate
Else
'If column cannot be parsed or it is null
Row.OutPutColumn_IsNull = True
End If
'Assuming that SCBActualMIN is a Date
If Not Row.SCBActualMIN_IsNull AndAlso
Not String.IsNullOrEmpty(Row.SCBActualMIN.Trim) AndAlso
DateTime.TryParse(Row.SCBActualMIN.Trim, dtDate)Then
Row.OutPut2Column = dtDate
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
Having a large amount of lines of code is not an issue if performance is good or optimal, Also minimizing the number of lines of code will not necessary improve the performance
如果性能是好的或最佳的，那么拥有大量的代码并不是问题，并且最小化代码行数将不需要提高性能。
Useful Links on SSIS , SQL , .Net Data Types
关于SSIS、SQL、。net数据类型的有用链接
- https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
- https://msdn.microsoft.com/en-us/library/cc716729(v = vs.110). aspx
- http://www.sqlservergeeks.com/sql-server-and-ssis-data-types/
- http://www.sqlservergeeks.com/sql-server-and-ssis-data-types/
- http://bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
- http://bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
- https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/data-flow/working-with-data-types-in-the-data-flow#mapping-data-types-in-the-data-flow
- https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/data-flow/working-with-data-types-in-the-data-flow mapping-data-types-in-the-data-flow
- https://docs.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types
- https://docs.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types
#1
2
9764377211 is bigger than 2147483647 which is the maximum value that can be assigned to Int32
9764377211大于2147483647，这是可以分配给Int32的最大值
Try converting value to Int64 or Double instead of Int32
尝试将值转换为Int64或Double，而不是Int32
Dim MIN As Int64
If Int64.TryParse(Row.SCBActualMIN.Trim, MIN) Then
Row.OutPut2Column = MIN
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
OR
或
Dim MIN As Double
If Double.TryParse(Row.SCBActualMIN.Trim, MIN) Then
Row.OutPut2Column = MIN
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
You cannot avoid repetition because you have to check each column for null by using it is own _IsNull property. You can try minimizing code by creating functions.
您不能避免重复，因为您必须通过使用它的_IsNull属性来检查每个列是否为空。您可以尝试通过创建函数来最小化代码。
#2
2
First of all, like @Yahfoufi suggested the exception is thrown because the value "9764377211 is bigger than 2147483647 which is the maximum value that can be assigned to Int32..."
首先，@Yahfoufi建议抛出异常，因为“9764377211大于2147483647，这是可以分配给Int32的最大值…”
You can Read more in this MSDN article about Data Types and corresponding Maximum values.
您可以在这篇MSDN文章中阅读更多关于数据类型和相应的最大值的文章。
Type Storage size Range
Int32 4 bytes -2,147,483,648 to 2,147,483,647
Int64 8 bytes Approximately -9.2E+18 to 9.2E+18
Double 8 bytes Approximate range is -1.79E+308 to 1.79E+308 with accuracy of about 15 digits. Can represent numbers as small as 1E-323.
Decimal 12 bytes Approximate range is -7.9E+28 to 7.9E+28 with accuracy of 28 digits. Can represent numbers as small as 1E-28.
...
You can assign this value to Int64 or Decimal or Double Data Types
可以将此值赋给Int64或Decimal或Double数据类型
Optimizing your code
优化代码
In this case you cannot avoid the repetition of these parts of code because there are using independent properties that cannot be called dynamically:
在这种情况下，您无法避免重复代码的这些部分，因为使用了不能动态调用的独立属性:
-
Checking if Input Columns is Null or Empty
检查输入列是否为空。
If Not Row.SCBActualMIN_IsNull AndAlso Not String.IsNullOrEmpty(Row.SCBActualMIN.Trim) Then -
Assigning
Nullto the output column if value cannot be parsed or input isNullor Empty如果值无法解析或输入为空或空，则将Null分配给输出列
Row.OutPut2Column_IsNull = True
You can modify your code to minimize number of lines but i don't think it will improve the performance
您可以修改代码以减少行数，但我认为这不会提高性能
- For each Data Type declare one variable inside the RowProcessing Sub and make your code as the following:
- 对于每个数据类型，在RowProcessing Sub中声明一个变量，并使代码如下所示:
Assuming that these 2 columns are containing dates
假设这两列包含日期
Dim dtDate As Date
If Not Row.SCBActualDTime_IsNull AndAlso
Not String.IsNullOrEmpty(Row.SCBActualDTime.Trim) AndAlso
DateTime.TryParse(Row.SCBActualDTime.Trim, dtDate)Then
Row.OutPutColumn = dtDate
Else
'If column cannot be parsed or it is null
Row.OutPutColumn_IsNull = True
End If
'Assuming that SCBActualMIN is a Date
If Not Row.SCBActualMIN_IsNull AndAlso
Not String.IsNullOrEmpty(Row.SCBActualMIN.Trim) AndAlso
DateTime.TryParse(Row.SCBActualMIN.Trim, dtDate)Then
Row.OutPut2Column = dtDate
Else
'If column cannot be parsed
Row.OutPut2Column_IsNull = True
End If
Having a large amount of lines of code is not an issue if performance is good or optimal, Also minimizing the number of lines of code will not necessary improve the performance
如果性能是好的或最佳的，那么拥有大量的代码并不是问题，并且最小化代码行数将不需要提高性能。
Useful Links on SSIS , SQL , .Net Data Types
关于SSIS、SQL、。net数据类型的有用链接
- https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
- https://msdn.microsoft.com/en-us/library/cc716729(v = vs.110). aspx
- http://www.sqlservergeeks.com/sql-server-and-ssis-data-types/
- http://www.sqlservergeeks.com/sql-server-and-ssis-data-types/
- http://bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
- http://bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
- https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/data-flow/working-with-data-types-in-the-data-flow#mapping-data-types-in-the-data-flow
- https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/data-flow/working-with-data-types-in-the-data-flow mapping-data-types-in-the-data-flow
- https://docs.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types
- https://docs.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types